触发器三例

 

一：设置自增字段

Create Or Replace Trigger stu_insert_tri
Before insert
on student
for each row
begin
select STUNO_SQU.NEXTVAL into :new.id from dual;
end;

二。添加表操作记录

create or replace trigger operation
before
delete or insert or update of grade on student
for each row
begin
if inserting then
INSERT INTO OPERATION_LOG VALUES(OPERATION_ID.NEXTVAL,USER,SYSDATE,'插入',:NEW.id,NULL,:NEW.grade);
end if;
if deleting then
INSERT INTO OPERATION_LOG VALUES(OPERATION_ID.NEXTVAL,USER,SYSDATE,'插入',:NEW.id,:old.grade,null);
end if;
if updating then
INSERT INTO OPERATION_LOG VALUES(OPERATION_ID.NEXTVAL,USER,SYSDATE,'插入',:NEW.id,:old.grade,:NEW.grade);
end if;
end;

三：一表更新操作对另一表进行更新

create or replace trigger update_college
after
update of id on college
for each row
begin
if updating then
update student set frist_will=:new.id where frist_will=:old.id;
update student set second_will=:new.id where second_will=:old.id;
update student set mat_colleage=:new.id where mat_colleage=:old.id;
end if;
end;