469,082 Members | 1,109 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,082 developers. It's quick & easy.

Trigger and DML INSERT on separate table

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.
Jul 19 '05 #1
3 6730

"Tom Urbanowicz" <ts*@landacorp.com> wrote in message
news:6d*************************@posting.google.co m...
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
Jul 19 '05 #2

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
Jul 19 '05 #3
Thanks very much for the help; works as needed!
-Tom

andrewst <me*********@dbforums.com> wrote in message news:<30****************@dbforums.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?

Jul 19 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Trevor Fairchild | last post: by
6 posts views Thread by Scott CM | last post: by
2 posts views Thread by Jules Alberts | last post: by
reply views Thread by JohnO | last post: by
5 posts views Thread by Bob Stearns | last post: by
12 posts views Thread by Zvonko | last post: by
3 posts views Thread by Tom Urbanowicz | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.