oracle trigger pl/sql
时间：2010-09-08 来源：kelin1314
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;
相关阅读 更多 +
