oracle trigger pl/sql

a sample of the trigger:

--the occasion before running the trigger

SQL> select * from a

  2  /

 

  DEPARTNO DEPARTNAME NUM(PK)

---------- --------------------

       100 部门一  1

       200 部门二  2

       300 部门三  3

 

SQL> select * from c

  2  /

 

      C_NO C_NAME                      DEP

---------- -------------------- ----------

         1 1                           100

         2 2                           200

--run the create trigger sentence

create or replace trigger my_trigger after update on scott.a for each row

begin

    update scott.c set dep=:new.departno

    where dep=:old.departno;

end;

Trigger created

--trigger the constrain and the event will be executed.

SQL> update a set departno=500 where departno=100;

--you can refer to the following result:

SQL> select * from a

  2  /

 

  DEPARTNO DEPARTNAME                  NUM

---------- -------------------- ----------

       500 部门一                        1

       200 部门二                        2

       300 部门三                        3

 

SQL> select * from c

  2  /

 

      C_NO C_NAME                      DEP

---------- -------------------- ----------

         1 1                           500

         2 2                           200

 

 temp table:new and :old

 ---after insert，then we only read :new 

create or replace trigger my_trigger after insert on a for each row

begin

dbms_output.put_line(:new.departno);

end;

---after update，then we read both :old and :new 

create or replace trigger my_trigger after update on a for each row

begin

dbms_output.put_line(:old.departno);

dbms_output.put_line(:new.departno);

end;

---after delete，then we only read :old 

create or replace trigger my_trigger after delete on a for each row

begin

dbms_output.put_line(:old.departno);

end;

 

 sql output --> file

spool $path;

spool off;

quit;