Trigger and DML INSERT on separate table

[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_APPLICATI ON_ERROR(-20001, 'Problem here. . .');
dbms_job.submit (test_job,'inse rt into MYTABLE (COL1, COL2)
values ('THIS WAS', 'AN ERROR'); commit;', NULL);
dbms_job.run(te st_job, false);
END IF;
END;
/

Thanks.

[Alan Mills]

Re: Trigger and DML INSERT on separate table


"Tom Urbanowicz" <tsu@landacorp. comwrote in message
news:6d63e629.0 307091402.45924 bf@posting.goog le.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_APPLICATI ON_ERROR(-20001, 'Problem here. . .');
dbms_job.submit (test_job,'inse rt into MYTABLE (COL1, COL2)
values ('THIS WAS', 'AN ERROR'); commit;', NULL);
dbms_job.run(te st_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

[andrewst]

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_APPLICATI ON_ERROR(-20001, 'Problem here. . .');
dbms_job.submit (test_job,'inse rt into MYTABLE (COL1, COL2)
values ('THIS WAS', 'AN ERROR'); commit;', NULL);
dbms_job.run(te st_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_APPLICATI ON_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_TRAN SACTION 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_TRAN SACTION;
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_APPLICATI ON_ERROR(-20001, 'Problem here. . .');
END IF;
END;
/

--
Posted via http://dbforums.com

[Tom Urbanowicz]

Re: Trigger and DML INSERT on separate table

Thanks very much for the help; works as needed!
-Tom

andrewst <member14183@db forums.comwrote in message news:<3094501.1 057832830@dbfor ums.com>...

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?