By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,060 Members | 1,906 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,060 IT Pros & Developers. It's quick & easy.

Trigger and DML INSERT on separate table

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a

"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

P: n/a

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

P: n/a
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.