Good Morning, We have a table - I'll call it *import_job* (which is the actual name) - that lists jobs to be executed. Each job has one or more child components listed in another table called *import_file*.
The child table has a foreign key column called *import_job_oid* referencing the primary key in *import_file*. When a record in *import_job* is deleted, the child records (file records) in *import_file* need to be deleted first. The constraint in both Oracle and Postgres is similar (Postgres version): *ALTER TABLE IF EXISTS idev.import_file* * ADD CONSTRAINT fk1_import_file FOREIGN KEY (import_job_oid) REFERENCES idev.import_job (oid) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE;* The files are appropriately deleted in Oracle, but Postgres is returning the following: *ERROR: Attempt to suppress referential action with before trigger. CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1 OPERATOR(pg_catalog.=) "import_job_oid"" * There aren't any delete triggers for either table. Any idea why this isn't working? Does cascade function differently in Postgres? Read the docs, Googled the heck out of this and played all sorts of games with the tables. I've also tried creating a before trigger on import_job, but can't seem to get the right syntax for taking the oid from the psql delete picked up by the trigger. Here is one of my (many) attempts (have tried describing, setting, using new.oid, old.oid, a bunch of stuff) and can't get this right either: *CREATE OR REPLACE FUNCTION idev."td_import_job$import_job"() ** RETURNS trigger ** LANGUAGE 'plpgsql' ** VOLATILE NOT LEAKPROOF **AS $BODY$ * *BEGIN ** RAISE NOTICE 'Value %', new.oid ** DELETE FROM idev.import_file ** WHERE import_job_oid = new.oid; **RETURN OLD; **END; **$BODY$; * *delete from idev.import_job where oid = 44949; * *NOTICE: Value <NULL> * *ERROR: Attempt to suppress referential action with before trigger. * *CONTEXT: SQL statement "DELETE FROM ONLY "idev"."import_file" WHERE $1 OPERATOR(pg_catalog.=) "import_job_oid" * -- *David A. Barbour* *dbarb...@istation.com <dbarb...@istation.com>* *(214) 292-4096* Istation 8150 North Central Expressway, Suite 2000 Dallas, TX 75206 www.Istation.com <http://www.istation.com/> CONFIDENTIALITY / PROPRIETARY NOTICE: The information contained in this e-mail, including any attachment(s), is confidential information that may be privileged and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or if you received this message in error, then any direct or indirect disclosure, distribution or copying of this message is strictly prohibited. If you have received this message in error, please notify Istation by calling 866-883-7323 immediately and by sending a return e-mail; delete this message; and destroy all copies, including attachments. Thank you.