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
dbarbour@istation.com
(214) 292-4096
Istation
8150 North Central Expressway, Suite 2000
Dallas, TX 75206
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.