在PL-SQL中编写触发器.docVIP

在PL/SQL中编写触发器 ----------------------------------触发器------------------------------- --创建测试表-- create table t1( t1_id number primary key, t1_name varchar2(20) ) --创建序列-- drop sequence seq_t1_id create sequence seq_t1_id --测试-- insert into t1 (t1_id,t1_name) values(seq_t1_id.nextval,a); insert into t1 (t1_id,t1_name) values(seq_t1_id.nextval,b); insert into t1 (t1_id,t1_name) values(seq_t1_id.nextval,c); commit; --编写触发器，实现id的自动增长-- create or replace trigger tr_t1_autoId before insert on t1 for each row begin select seq_t1_id.nextval into :new.t1_id from dual; end; insert into t1 (t1_name) values(d); insert into t1 (t1_name) values(e); insert into t1 (t1_name) values(f); commit; ----实现工资只能增加，不能减少-- create or replace trigger tr_emp_sal before update on emp for each row declare begin if (:old.sal:new.sal) then raise_application_error(-20009,老板，我每天都努力工作，怎么能减我的工资呢); end if; end; update emp set sal=1000 where empno=7002; ---使用条件谓词--------------------- --更新员工工资，只能增加，不能减少;删除员工，不能删除项目经理-- create or replace trigger tr_emp_sal before update or delete on emp for each row begin if updating then if (:old.sal:new.sal) then raise_application_error(-20009,老板，我每天都努力工作，怎么能减我的工资呢); end if; end if; if deleting then if(:old.job=MANAGER) then raise_application_error(-20009,不能删除项目经理); end if; end if; end; select * from emp delete from emp where empno=7566 ---添加员工，超过3人不能添加-- create or replace trigger tr_emp_add before insert on emp for each row declare v_count number; begin select count(*) into v_count from emp where deptno=:new.deptno; if v_count=3 then raise_application_error(-20010,此部门人数太多，不能再添加); end if; end; select deptno,count(*) as counts from emp group by deptno insert into emp (empno,ename,sal,deptno) values(6001,大傻,3000,50) ----修改部门编号，该部门的员工部门编号也随之变化--- create or replace trigger tr_dept_update_deptno after update of deptno on dept for each row begin upda