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