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

在PL/SQL中编写触发器-触发器-创建测试表-create table t1( t1_id number primary key, t1_name varchar2(20)-创建序列-drop sequence seq_t1_idcreate 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_autoIdbefore inserton t1for each rowbegin 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_salbefore updateon empfor each rowdeclarebegin 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_salbefore update or deleteon empfor each rowbegin 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 empdelete from emp where empno=7566-添加员工，超过3人不能添加-create or replace trigger tr_emp_addbefore inserton empfor each rowdeclare 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 deptnoinsert into emp (empno,ename,sal,deptno) values(6001,大傻,3000,50)-修改部门编号，该部门的员工部门编号也随之变化-create or replace trigger tr_dept_update_deptnoafter update of deptnoon deptfor each rowbegin update emp set deptno=:new.deptno where deptno=:old.deptno;end;-测试-select * from deptselect * from empupdate dept set deptno=21 where deptno=20-看新记录的id是几？-create or replace trigger tr_t1before inserton t1for each rowdeclare v_max t1.t1_id%type;begin select max(t1_id) into v_max from t1; dbms_output.put_line(v_max); :new.t1_id :=nvl(v_max,0)+1;end;-测试-select * from t1delete from t1insert into t1(t1_id,t1_name) values(2,d);-before和after的区别-任务一、t1用最大编号+1 实现自动编号，名称转化为大写。-由于修改了:new变量，所以只能用Before。create or replace trigger tr_t1before insert on t1for each rowdeclare id_max t1.t1_id%type;begin select nvl(max(t1_id),0)+1 into id_max from t1; :new.t1_id :=id_max; :new.t1_name :=upper(:new.t1_name);end;-添加t1的时候，把记录同时添加到t2-sql中复制表select * into t2 from t1 create table t2 as select * from t1-复制表-由于只是使用:new变量，并没有修改，所以-before和after都可以create or replace trigger tr_t1_insertafter insert on t1for each rowbegin insert into