ORA-04092: cannot COMMIT in a trigger

create a audit table to record modifications on table t1 as follows:


create or replace trigger t1_tgr
after insert or delete or update on t1
for each row
begin
if inserting then
insert into t1_audit values(:new.id, 'INSERT', dbms_flashback.get_system_change_number, '', :new.a);
commit;
elsif deleting then
insert into t1_audit values(:old.id, 'DELETE', dbms_flashback.get_system_change_number, :old.a, '');
commit;
elsif updating then
insert into t1_audit values(:old.id, 'UPDATE', dbms_flashback.get_system_change_number, :old.a, :new.a);
commit;
end if;
end;

when inserting a new row into t1, we got this error


insert into t1 values('insert a new line',999999);
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "GUANG.T1_TGR", line 4
ORA-04088: error during execution of trigger 'GUANG.T1_TGR'

as explained in metalink


OERR: ORA 4092 cannot COMMIT or ROLLBACK in a trigger [ID 19880.1]

Error: ORA 4092 Text: cannot COMMIT or ROLLBACK in a trigger
-------------------------------------------------------------------------------
Cause: A trigger attempted to COMMIT or ROLLBACK. This is not permitted.
Action: Rewrite the trigger so that COMMIT or ROLLBACK statements are not used</span>

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: