oracle触发器练习

1、DML 触发器： 对表执行Insert、Update、Delete操作时激发 可以用于执行校验、设置初使值、审核改变、甚至禁止某种DML操作 语法： CREATE OR REPLACE TRIGGER 触发器名称 AFTER|BEFORE - 指定触发时机 INSERT OR DELETE OR UPDATE - 指定触发器事件 ON 表名 -指定所监控的表 FOR EACH ROW|FOR EACH STATEMENT - 指定触发器次数 BEGIN -代码; END; 相关概念： AFTER|BEFORE：在什么事件之前或之后执行 INSERT|DELETE|UPDATE：什么事件 ON

2、表名：触发器建在什么表上，即监控什么表 FOR EACH ROW：行级触发，示例：delete from t1,删除1000行，则执行1000次(一行一次 FOR EACH STATEMENT：语句级触发，示例： delete from t1,删除1000行，则执行1次(一句一次 :new 行变量：保存事件发生时新数据所在行，只有insert事件和update事件才有新数据 :old 行变量：保存事件发生时旧数据所在行，只有delete事件和update事件才有旧数据 示例：指出事件，及事件中的新数据和旧数据 insert into emp(empno,ename values(51,'

3、;job' 分析：只有一行新数据(51,job，对应:new变量。 update emp set ename='oracle' where empno = 51; 分析：旧数据 (51,job ，对应:old变量。 新数据(51,oracle，对应:new变量。 delete from emp where empno = 51; 分析：只有一行旧数据(51,oracle,对应:old变量。 Sql代码 - 显示触发器 select trigger_name,status from user_triggers; - 禁止触发器 alter trigger tr_emp_s

4、alary disable; - 激活触发器 alter trigger tr_emp_salary enable; - 禁止表的所有触发器 alter table employee disable all triggers; - 激活表的所有触发器 alter table employee enable all triggers; - 重新编译触发器 -alter table tr_emp_salary compile; - 删除触发器 drop trigger tr_emp_salary; - 显示触发器 select trigger_name,status from user_trigg

5、ers; - 禁止触发器 alter trigger tr_emp_salary disable; - 激活触发器 alter trigger tr_emp_salary enable; - 禁止表的所有触发器 alter table employee disable all triggers; - 激活表的所有触发器 alter table employee enable all triggers; - 重新编译触发器 -alter table tr_emp_salary compile; - 删除触发器 drop trigger tr_emp_salary; Sql代码 - 语句级触发器

6、- 禁止员工在休息日改变雇员信息 create or replace trigger tr_sec_emp - before:在DML事件之前执行 before insert or update or delete on employee - 如果在这里没有指定是行级还是语句级触发器，默认就是 FOR EACH STATEMENT begin if to_char(sysdate,'DAY','nls_date_language=AMERICAN' in('SAT','SUN' then case - 当触发事件是Insert操作

7、时，该条件谓词返回值为True，否则为False when i nserting then raise_application_error(-20001,'不能在休息日增加雇员信息！' - 当触发事件是Update操作时，该条件谓词返回值为True，否则为False when updating then raise_application_error(-20002,'不能在休息日修改雇员信息！' - 当触发事件是Delete操作时，该条件谓词返回值为True，否则为False when deleting then raise_application_error(

8、-20003,'不能在休息日删除雇员信息！' end case; end if; end; / - 语句级触发器 - 禁止员工在休息日改变雇员信息 create or replace trigger tr_sec_emp - before:在DML事件之前执行 before insert or update or delete on employee - 如果在这里没有指定是行级还是语句级触发器，默认就是 FOR EACH STATEMENT begin if to_char(sysdate,'DAY','nls_date_language=AMERIC

9、AN' in('SAT','SUN' then case - 当触发事件是Insert操作时，该条件谓词返回值为True，否则为False when inserting then raise_application_error(-20001,'不能在休息日增加雇员信息！' - 当触发事件是Update操作时，该条件谓词返回值为True，否则为False when updating then raise_application_error(-20002,'不能在休息日修改雇员信息！' - 当触发事件是Delete操作时，该条

10、件谓词返回值为True，否则为False when deleting then raise_application_error(-20003,'不能在休息日删除雇员信息！' end case; end if; end; / Sql代码 - 限制员工的工资不能超过当前的最高工资 create or replace trigger tr_emp_salary before - update of 后面指定在那些列被改变时才调用这个触器，如果不加of 默认是所有列 update of salary on employee - 指定该触发器为行级触发器 for each row - 如

11、果要定义变量，则只能在Declare中定义 declare maxSalary number(10,2; begin select max(salary into maxSalary from employee; if :new.salary > maxSalary then raise_application_error(-20010,'员工工资超出工资上限！' end if; end; / - 限制员工的工资不能超过当前的最高工资 create or replace trigger tr_emp_salary before - update of 后面指定在那些列被改

12、变时才调用这个触器，如果不加of 默认是所有列 update of salary on employee - 指定该触发器为行级触发器 for each row - 如果要定义变量，则只能在Declare中定义 declare maxSalary number(10,2; begin select max(salary into maxSalary from employee; if :new.salary > maxSalary then raise_application_error(-20010,'员工工资超出工资上限！' end if; end; / Sql代码

13、- 设置员 if; end; / - 创建用户表 - drop table users; create table users( userId int not null, username varchar(20 not null, password varchar2(50 not null, logintime date not null - 向用户表中插入数据 insert into users values(1,'user1',11111,sysdate; insert into users values(2,'user2',22222,sysdate; i

14、nsert into users values(3,'user3',33333,sysdate; insert into users values(4,'user4',44444,sysdate; insert into users values(5,'user5',55555,sysdate; select * from users; - 建立复杂视图 - drop view emp_users; create or replace view emp_users as select distinct e.empno,,u.usern

15、ame from employee e,users u where e.empno = u.userId; select * from emp_users; - drop view tr_instead_of_emp_users; create or replace trigger tr_instead_of_emp_users instead of insert on emp_users for each row declare i_temp int; begin - 同时向两张表中插入数据 select count(* into i_temp from employee where emp

16、no = :new.empno; if i_temp = 0 then insert into Employee values(:new.empno,:,'销售经理',1,'2009-01-09',5300,'销售',4; end if; select count(* into i_temp from users where userId = :new.empno; if i_temp = 0 then insert into users values(:new.empno,:new.username,55555,sysdate;

17、 end if; end; / Sql代码 - 测试Instead of 触发器 insert into emp_users values(10,'王五','小李' insert into emp_users values(11,'张三','小明' select * from emp_users; - 测试Instead of 触发器 insert into emp_users values(10,'王五','小李' insert into emp_users values(11,'张三&#

18、39;,'小明' select * from emp_users; 数据库事件触发器： 在数据库启动、关闭、用户登录、退出或者Oracle错误发生时，以及执行创建、删除表、索引等DDL语句时激发 主要用于跟踪数据库活动 创建打开数据库触发器：after startup 创建用户登录触发器：after logon 创建用户退出触发器：before logoff 创建DDL触发器：after ddl Sql代码 - 建立用户登录的触发器 create or replace trigger tr_logon after logon on database begin raise_application_error(-20001,ora_login_user | '用户登录数据库！' end; / - 建立用户退出的触发器 create or replace trigger t