PL/SQL基础篇10(trigger)

 触发器：

/*

   触发器：当特定事件出现事自动执行的存储过程，不能被显试地调用

   功能：1.自动生成数据；2.自定义复杂的安全权限；3.提供审计和日志记录；4.启用复杂的业务逻辑

*/

create table deptinfo(

       deptid number primary key,

       dname varchar2(20),

       empnum number

)

create table employee(

       eid number primary key,

       ename varchar2(20),

       deptid number references deptinfo(deptid)

)

-- 创建触发器，关键字：trigger，after，before，on，for each row

create or replace trigger tri_deptNum

after -- 有before after instead of

insert or update or delete on employee -- 对employee操作

for each row -- 注意别忘了 for each row

begin

  if inserting then -- 用ing形式

    update deptinfo set empnum = empnum + 1 where deptid = :new.deptid;

  elsif updating then -- ing形式，去掉e，与英文语法一致

    update deptinfo set empnum = empnum - 1 where deptid = :old.deptid;

    update deptinfo set empnum = empnum + 1 where deptid = :new.deptid;

  elsif deleting then

    update deptinfo set empnum = empnum - 1 where deptid = :old.deptid;

  end if;

end;

-- 测试

select * from deptinfo;

insert into deptinfo values(1,'长安大学',0);

insert into deptinfo values(2,'helloworld',0);

insert into employee values(1,'ysjain',1);

insert into employee values(2,'ysjain',1);

insert into employee values(3,'ysjain',2);

insert into employee values(4,'ysjain',2);

delete from employee where eid = 1;

update employee set deptid = 2 where deptid = 1;

select * from employee;