Thread: Trigger question
I would like to prevent changes being made to a table (b), except when a trigger fires as a result of changes (insert,update) being made to another table (a). Should any changes be made to table b by any other means than the trigger from table a, I would like to trap those changes (again, could I use a trigger?) and make sure that the original values in table b stays the same. Can anybody point me in the right direction please. Alex du Plessis
On Friday 22 February 2008 05:24, Alex du Plessis wrote: > I would like to prevent changes being made to a table (b), except when a > trigger fires as a result of changes (insert,update) being made to > another table (a). Should any changes be made to table b by any other > means than the trigger from table a, I would like to trap those changes > (again, could I use a trigger?) and make sure that the original values > in table b stays the same. > > Can anybody point me in the right direction please. > I suppose there are several ways to accomplish this. One is to create a small table explicitly for this purpose containing whatever you want but making sure that the table has the backend pid stored in it. You can have an AFTER trigger on table a insert data into this table and you can have a BEFORE trigger on table b check for the existence of a record with the same pid and if found, read data out of the table and make the update. Then, delete the table entry. Just one way... -- Terry Lee Tucker Turbo's IT Manager Turbo, division of Ozburn-Hessey Logistics 2251 Jesse Jewell Pkwy NE Gainesville, GA 30501 Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987 terry@turbocorp.com www.turbocorp.com
I'm getting an error from within a stored procedure being called from Coldfusion that is telling me that a foreign key value is not present in the parent table, but it clearly is. This happens infrequently, but seems to continue to happen despite restarts. Detailed message: EXCEPTION MESSAGE: Error Executing Database Query. DETAIL: [Macromedia][SequeLink JDBC Driver][ODBC Socket]ERROR: insert or update on table "entity_documentation" violates foreign key constraint "entity_documentation_distributor_id_fkey" Key (distributor_id)=(87) is not present in table "distributors".; Error while executing the query NATIVEERRORCODE: 7 SQLSTATE: 23503 QUERYERROR: [Macromedia][SequeLink JDBC Driver][ODBC Socket]ERROR: insert or update on table "entity_documentation" violates foreign key constraint "entity_documentation_distributor_id_fkey" Key (distributor_id)=(87) is not present in table "distributors".; Error while executing the query ERRORCODE: 23503 TIA!