SQL> update dept1 set dname='new accounting' where deptno=1;
update dept1 set dname='new accounting' where deptno=1
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.T_DEPT1", line 3
ORA-04088: error during execution of trigger 'SCOTT.T_DEPT1'

SQL>

####说明在触发器内部不能有commit或者rollback，触发器会自动提交的哟
SQL> create or replace trigger t_dept1
2 after update of dname on dept1 for each row
3 begin
4 insert into dept1_audit values(:old.deptno,sysdate,:new.dname,:old.dname);
5 end;
6 /

Trigger created.

SQL> update dept1 set dname='new accounting' where deptno=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from dept1_audit;###更新了表的某行记录后，触发器发生了作用，把变更记录的新老记录存储在此表中

DEPTNO UP_DATE NEW_DNAME OLD_DNAME
---------- ------------ ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 22-7月 -10 new accounting

ITPub博客

oracle trigger语法小记（一）

最新文章