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. . .');
END IF;
END;
/
--
Posted via
http://dbforums.com 
