oracle trigger语法小记(一)

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
        

请使用浏览器的分享功能分享到微信等