Re: Trigger and DML INSERT on separate table

Originally posted by Tom Urbanowicz
> 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 are testing whether mytestvar > 0 but you never gave it a value, so it will not be.

Also, once you have called RAISE_APPLICATION_ERROR, the trigger is aborted (exception raised), so it will never get to the dbms_job.submit line.

I guess you are trying to use dbms_job to overcome the fact that if the triggering statement rolls back, so would any insert into mytable. That isn't the right approach. Instead, you should use PRAGMA AUTONOMOUS_TRANSACTION to commit the insert into mytable regardless of whether the main transaction is committed or rolled back, perhaps like this:

CREATE OR REPLACE PROCEDURE log_error
( p_error_text1 IN VARCHAR2
, p_error_text2 IN VARCHAR2
)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO mytable (col1, col2)
VALUES (p_error_text1, p_error_text2);
COMMIT;
END;
/

CREATE OR REPLACE TRIGGER test
AFTER INSERT OR DELETE OR UPDATE ON testdb FOR EACH ROW
DECLARE
  mytestvar number := 99;
BEGIN
   IF mytestvar > 0 THEN

       log_error( 'THIS WAS', 'AN ERROR');
       RAISE_APPLICATION_ERROR(-20001, 'Problem here. . .');

--
Posted via http://dbforums.com