I have two tables one ordinary table and other is log of this table
test_table
test_tab_log
I want to insert new row in the log table for each insert statement executed for basic table even if there is rollback executed.
simply every row inserted in the base table need to be logged in the log table even if one or more row not commit.
so what i am trying to do is using commit inside trigger definition, but it’s not allowed and throw this error while trying to insert any row into base table.
ORA-04092: cannot COMMIT in a trigger.
how can do this ?
Hello albert
Actually we cannot do commit or rollback inside a database trigger, because the trigger is an extension of a DML statements (Insert & Update & Delete). So the changes happened inside trigger should be committed or rollbacked as part of the original transaction.
So if you are traying to do commit inside the trigger you will get clear exception which is :
Hi,
You can creater a procedure to insert into your test_table_log table with parameter of the columns of thie table.
Then on your test_table table trigger call this procedure by passing the values.
Commit will be in your calling procedure. So it will work.