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

1、1触发器语法：如：SQL create or replace trigger trg_emp2 after update of sal3 on t_emp4 for each row5 begin26 if :NEW.sal 3000 then7 dbms_output.put_line(:OLD.ENAME|步入高薪行列！);8 else9 dbms_output.put_line(:OLD.ENAME|依然贫穷！);10 end if;11 end;12 /触发器已创建SQL update t_emp set sal=sal+100 where deptno=20;SMITH依然贫穷！JO

2、NES步入高薪行列！SCOTT步入高薪行列！ADAMS依然贫穷！FORD步入高薪行列！已更新5行。*-将用户对emp表的所有操作记录日志？Rem 数据准备Rem =Rem 企业部门机构信息表 T_DEPT_INFORem 部门编号 Rem 部门名称 Rem 是否有上级 Rem 上级部门编号Rem 信息校验 Rem =3CREATE TABLE T_DEPT_INFO(DEPTNO NUMBER(4) CONSTRAINT PK_DEPT_INFO PRIMARY KEY,DNAME VARCHAR2(20) ,IS_SUPER_DEPT NUMBER(1),SUPER_DEPTNO NUMBE

3、R(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),4ORGIN VARCHAR2(30),B

4、IRTHDAY 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,企业最高行政机构);inse

5、rt 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,开拓企业市场

6、);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,网

7、络安全产品研发);insert into T_DEPT_INFO(DEPTNO,DNAME,IS_SUPER_DEPT,SUPER_DEPTNO,LOG) values(3002,研发二部,1,2002,短信平台产品研发);5insert 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项目组

8、,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

9、,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

10、 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,

11、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) 6values(200102,翠花,432425751120019,女,湖南常德,to_date(1987-05-10,yyyy-mm-dd),本科,财务助理,sysdate,

12、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

13、,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-

14、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,BIRTHD

15、AY,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-

16、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-706-25,yyyy-mm-dd),本科,售后服务,sysdate,2500,3004,主管企业后勤);insert into T_EMP_INFO(EMPNO,ENAME,CARDID,S

17、EX,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,男,湖南

18、长沙,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,E

19、NAME,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(8who VARCHAR2(30),when DATE);Rem 在测试表上面创建触发器，它将会在语句触发

20、器中填充日志表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_ch

21、ar(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;9Rem =Rem 2、触发器中起作用的条件谓词：inserting,updating,deletingRem 如果这些这些条件谓词的值为真，那么就是相应的语句类型初始化了您的触发器Rem =Rem 修改日志表结构alter table t_logadd operation varchar2(20);desc t_log;Rem 修改触发器CREATE OR REPLACE

22、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 then10l_operation :=update operation;elsif deleting thenl_operation :=delete operation;elserai

23、se_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_edubefore insert or updateof EDUCATIONA on t_emp_inforeferencing old as old_value new as new_valuefor each row