Re: Trigger and DML INSERT on separate table

From: Alan Mills <Alan.Mills_at_xservices.pants.fujitsu.com>
Date: Thu, 10 Jul 2003 10:13:44 +0100
Message-ID: <bejapd$hfg$1_at_news.icl.se>


"Tom Urbanowicz" <tsu_at_landacorp.com> wrote in message news:6d63e629.0307091402.45924bf_at_posting.google.com...
> I have a trigger. Each time the triggering event fires, I want to
> insert some information into another 'audit-like' table (not at all
> related to the trigger). Some psuedo-code is below as to what I've
> been working with; I am unable to get the INSERT to work?
> Recommendations; what am I doing wrong?
> -------------------
>
>
> CREATE OR REPLACE TRIGGER test
> AFTER INSERT OR DELETE OR UPDATE ON testdb
> FOR EACH ROW
> DECLARE
> mytestvar number;
>
> test_job number;
> BEGIN
> IF mytestvar > 0 THEN
> RAISE_APPLICATION_ERROR(-20001, 'Problem here. . .');
> dbms_job.submit(test_job,'insert into MYTABLE (COL1, COL2)
> values ('THIS WAS', 'AN ERROR'); commit;', NULL);
> dbms_job.run(test_job, false);
> END IF;
> END;
> /
>
> Thanks.

You said that you are unable to get the insert to work. You did NOT say what error you are getting. Your inference to 'not at all related' implies to me you are getting a mutating tables error. If so then it's likely that you have foreign keys or some ogther constraint (which will act as a read) between the table with the trigger and the audit table that the trigger is hitting.

If it's somethign else then you'd best give us all more details.

HTH Alan Received on Thu Jul 10 2003 - 11:13:44 CEST

Original text of this message