oracle触发器的创建及使用!!.doc

触发器语法：如：SQL create or replace trigger trg_emp 2 after update of sal 3 on t_emp 4 for each row 5 begin 6 if :NEW.sal 3000 then 7 dbms_output.put_line(:OLD.ENAME|步入高薪行列！); 8 else 9 dbms_output.put_line(:OLD.ENAME|依然贫穷！); 10 end if; 11 end; 12 /触发器已创建SQL update t_emp set sal=sal+100 where deptno=20;SMITH依然贫穷！JONES步入高薪行列！SCOTT步入高薪行列！ADAMS依然贫穷！FORD步入高薪行列！已更新5行。*-将用户对emp表的所有操作记录日志？Rem 数据准备Rem=Rem 企业部门机构信息表 T_DEPT_INFORem部门编号 Rem部门名称 Rem是否有上级 Rem上级部门编号Rem信息校验 Rem=CREATE TABLE T_DEPT_INFO(DEPTNO NUMBER(4) CONSTRAINT PK_DEPT_INFO PRIMARY KEY,DNAME VARCHAR2(20) ,IS_SUPER_DEPT NUMBER(1),SUPER_DEPTNO NUMBER(4),LOG VARCHAR2(30) ) ;Rem=Rem 企业员工信息表 T_EMP_INFORem员工编号Rem员工姓名Rem身份证号Rem性别Rem家庭住址Rem生日日期Rem学历Rem工作职务Rem雇佣日期Rem薪水Rem所属部门编号Rem信息校验Rem=CREATE TABLE T_EMP_INFO(EMPNO NUMBER(6) CONSTRAINT PK_EMP_INFO PRIMARY KEY,ENAME VARCHAR2(10),CARDID VARCHAR2(18),SEX VARCHAR2(2),ORGIN VARCHAR2(30),BIRTHDAY DATE,EDUCATIONA VARCHAR2(6),JOB VARCHAR2(10),HIREDATE DATE,SAL NUMBER(7,2),LOG VARCHAR2(30),DEPTNO NUMBER(4) CONSTRAINT FK_DEPTNO_INFO REFERENCES T_DEPT_INFO);Rem=Rem 为企业部门机构信息 T_DEPT_INFO 表插入测试数据Rem =insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,LOG) values(1000,总经理办公室,0,企业最高行政机构);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(2001,财务办公室,1,1000,主管企业财务);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(2002,产品研发部,1,1000,主管企业产品研发);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(2003,市场部,1,1000,开拓企业市场);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(2004,售后服务部,1,1000,主管售后服务);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(2005,行政部,1,1000,主管企业行政工作);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(3001,研发一部,1,2002,网络安全产品研发);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(3002,研发二部,1,2002,短信平台产品研发);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(3003,研发三部,1,2002,企业项目开发);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(4001,OA项目组,1,3001,工商局OA项目开发);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(4002,电话银行项目组,1,3002,常德农信OA项目开发);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(4003,网络安全项目组,1,3003,常德建设银行网络安全建设);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(3004,后勤组,1,2005,主管企业后勤工作);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(3005,司机班,1,2005,主管企业车辆);commit;Rem=Rem 为企业员工信息 T_EMP_INFO 表插入测试数据Rem insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values();insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(100001,lemon,432425780313211,男,湖南常德,to_date(1978-03-13,yyyy-mm-dd),本科,总经理,sysdate,8000,1000,主管公司重大事务);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200101,田世峰,432425751120019,男,湖南常德,to_date(1975-11-20,yyyy-mm-dd),本科,财务经理,sysdate,5000,2001,主管公司财务);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200102,翠花,432425751120019,女,湖南常德,to_date(1987-05-10,yyyy-mm-dd),本科,财务助理,sysdate,2000,2001,协助财务工作);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200201,郭鹏,025425761120019,男,重庆北赔区,to_date(1976-05-10,yyyy-mm-dd),本科,技术总监,sysdate,7000,2002,主管公司技术);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200202,姚锋,010425760923024,男,北京朝阳区,to_date(1976-09-23,yyyy-mm-dd),本科,设计总监,sysdate,6500,2002,主管公司设计);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200301,于平,432425781025211,男,湖南常德,to_date(1978-10-25,yyyy-mm-dd),本科,市场经理,sysdate,6500,2003,主管市场开拓);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200302,陈亮,139425820625038,男,河北廊坊,to_date(1982-06-25,yyyy-mm-dd),本科,市场助理,sysdate,2500,2003,市场开拓);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200401,陈辉,139425820625038,男,河北沧州,to_date(1982-06-25,yyyy-mm-dd),本科,售后服务,sysdate,2500,2004,售后服务);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200402,江华,139425820625038,男,湖北汉口,to_date(1982-06-25,yyyy-mm-dd),本科,售后服务,sysdate,2500,2004,售后服务);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(300401,王敏,139425820625038,女,湖南长沙,to_date(1982-06-25,yyyy-mm-dd),本科,售后服务,sysdate,2500,3004,主管企业后勤);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(200403,陈辉1,139425820625038,男,河北沧州,to_date(1982-06-25,yyyy-mm-dd),本科,售后服务,sysdate,2500,2004,售后服务);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(400101,向辉,139425820625038,男,湖南长沙,to_date(1982-06-25,yyyy-mm-dd),本科,OA,sysdate,4500,4001,OA项目开发);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(400201,唐颖,139425820625038,男,湖南长沙,to_date(1982-06-25,yyyy-mm-dd),本科,OA,sysdate,4500,4002,电话银行开发);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,SEX,ORGIN,BIRTHDAY,EDUCATIONA,JOB,HIREDATE,SAL,DEPTNO,LOG) values(400301,唐泽军,139425820625038,男,湖南长沙,to_date(1982-06-25,yyyy-mm-dd),本科,OA,sysdate,4500,4003,电话银行开发);commit;Rem =Rem 1、语句级触发器Rem =Rem 创建一个向表中插入数据后，同时写日志的表CREATE TABLE t_log(who VARCHAR2(30),when DATE);Rem 在测试表上面创建触发器，它将会在语句触发器中填充日志表CREATE TRIGGER biud_emp_infoBEFORE INSERT OR UPDATE OR DELETEON t_emp_infobegininsert into t_log values(user,sysdate);end;Rem 查询测试表信息select empno,ename,sal from t_emp_info;Rem 做激发触发器工作的语句操作update t_emp_info set sal = sal*1.1;delete from t_emp_info where empno=400301;Rem 查看日志操作select who,to_char(when,yyyy-mm-dd hh:mm:ss) from t_log;select who,to_char(when,yyyy-mm-dd hh:mm:ss) time,operation from t_log;Rem =Rem 2、触发器中起作用的条件谓词：inserting,updating,deletingRem 如果这些这些条件谓词的值为真，那么就是相应的语句类型初始化了您的触发器Rem =Rem 修改日志表结构alter table t_logadd operation varchar2(20);desc t_log;Rem 修改触发器CREATE OR REPLACE TRIGGER biud_emp_infoBEFORE INSERT OR UPDATE OR DELETEON t_emp_infoFOR EACH ROW declarel_operation varchar2(20);-error_operator exception;beginif INSERTING THENl_operation :=Insert operation;elsif UPDATING thenl_operation :=update operation;elsif deleting thenl_operation :=delete operation;elseraise_application_error(-20001,wwwwwwwwww);end if;insert into t_log values(user,sysdate,l_operation);end;Rem =Rem 3、创建行级触发器for each rowRem 注意：前映像（Before image）的默认名称为：:old (insert触发器中没有:old或者前映像Rem 后映像（After image）的默认名称为：:new(delete触发器中没有:new或者后映像）Rem =create trigger biufer_emp_info_edubeforeinsert or updateof EDUCATIONA on t_emp_inforeferencing old as old_value new as new_valuefor each rowwhen (new_value.EDUCATIONA 本科)begin:new_value.sal := :old_value.sal/1.1;end;/update t_emp_info set EDUCATIONA=专科 where deptno=2004;commit;select empno,ename,sal,deptno,EDUCATIONA from t_emp_info;Rem =Rem 4、利用行触发器使用序列生成主键的例子Rem =-建表create table foo(id number primary key,data varchar2(100);-建序列create sequence seq_foo;-接下来的步骤是建立生成主键的行触发器create trigger bifer_foo_id_pkbefore insert on foofor each rowbeginselect seq_foo.nextval into :new.id from dual;end;/-现在我们就可以测试他insert into foo(data) values(lemon);insert into foo(id,data) values(5,rachel);column data format a30select *from foo;Rem =Rem 5、instead of触发器：是oracle用来替换所使用的实际语句而执行的触发器。听起来不是个好主意。Rem 但是，考虑在视图上进行i