【安博培训笔记】Oracle8 触发器 作业20130914

Oracle8 触发器 作业


emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)
dept部门表(deptno部门编号/dname部门名称/loc地点)
1.  禁止员工在休息日改变雇员信息
----------
CREATE OR REPLACE TRIGGER trig_stu
BEFORE UPDATE OF f_class
ON martin.t_student
FOR EACH ROW
WHEN (OLD.f_class=1)
BEGIN
  :NEW.f_class := 1;
END;
--------
create or replace trigger tri_emp1
before insert or update or delete on emp
begin
if to_char(sysdate,'DAY','nls_date_language=AMERICAN')
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操作时，该条件谓词返回值为True，否则为False
when deleting then
raise_application_error(-20003,'不能在休息日删除雇员信息！');
end case;
end if; 
end;
update emp set sal = sal+10 where empno = 7788;
select sal from emp where empno = 7788;
drop trigger tri_emp;
------------
        
2.  限制员工的工资不能超过当前的最高工资
----------------
create or replace trigger tr_emp_sal2
before update of sal on emp
for each row
declare
    max_sal emp.sal%type;  
begin
  select max(sal) into max_sal from emp;
  if :new.sal > max_sal then
     raise_application_error(-20010,'员工工资超出工资上限！'); 
  end if;
end;
/
----------------
-- 限制员工的工资不能超过当前的最高工资
create or replace trigger tr_emp_salary before
-- update of 后面指定在那些列被改变时才调用这个触器，如果不加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;
----------------
-- 限制员工的工资不能超过当前的最高工资
create or replace trigger tr_emp_salary before
-- update of 后面指定在那些列被改变时才调用这个触器，如果不加of 默认是所有列
update of sal on emp
-- 指定该触发器为行级触发器
for each row
-- 如果要定义变量，则只能在Declare中定义
declare
maxSalary number(10,2);
begin
select max(sal) into maxSalary from emp;
if :new.sal > maxSalary then
raise_application_error(-20010,'员工工资超出工资上限！');
end if;
end;


drop trigger tr_emp_salary
----------------
create or replace trigger tr_emp_sal2
before update of sal on emp
for each row
declare
    max_sal emp.sal%type;  
begin
  select max(sal) into max_sal from emp;
  if :new.sal > max_sal then
     raise_application_error(-20010,'员工工资超出工资上限！'); 
  end if;
end;
/
----------
insert into emp values(7777,'shihua','CLERK',7777,sysdate,77,77,20); 
select max(sal) from emp;
select * from emp;
select sal from emp where empno = 7777;
update emp set sal = 50 where empno = 7777;
update emp set sal = sal + 1 where empno = 7777;
select sal from emp where empno = 7788;
---------
drop trigger tr_emp_sal2;
--------
3.  设置员工的工资不能低于原工资，但也不能高出原工资的20%
--when (new.salary < old.salary or new.salary > old.salary*1.2)
create or replace trigger tr_emp_sal3
before update of sal on emp
for each row
when (new.sal < old.sal or new.sal > old.sal*1.2)
begin
     raise_application_error(-20003,'设置员工的工资不能低于原工资，但也不能高出原工资的20%');
end;
-----------
drop trigger tr_emp_sal3;
--------
update emp set sal = sal*1.3 where empno = 7788;
select sal from emp where empno = 7788;
------------------------------------
主题：Oracle学习笔记(8) 使用PLSQL编写触发器
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 表名：触发器建在什么表上，即监控什么表 
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,'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代码 
1.-- 显示触发器 
2.select trigger_name,status from user_triggers; 
3.-- 禁止触发器 
4.alter trigger tr_emp_salary disable; 
5.-- 激活触发器 
6.alter trigger tr_emp_salary enable; 
7.-- 禁止表的所有触发器 
8.alter table employee disable all triggers; 
9.-- 激活表的所有触发器 
10.alter table employee enable all triggers; 
11.-- 重新编译触发器 
12.--alter table tr_emp_salary compile; 
13.-- 删除触发器 
14.drop trigger tr_emp_salary; 
-- 显示触发器
select trigger_name,status from user_triggers;
-- 禁止触发器
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代码 
1.-- 语句级触发器 
2.-- 禁止员工在休息日改变雇员信息 
3.create or replace trigger tr_sec_emp 
4. -- before:在DML事件之前执行 
5. before insert or update or delete 
6. on employee 
7. -- 如果在这里没有指定是行级还是语句级触发器，默认就是 FOR EACH STATEMENT 
8.begin 
9. if to_char(sysdate,'DAY','nls_date_language=AMERICAN') 
10. in('SAT','SUN') then 
11. case 
12. -- 当触发事件是Insert操作时，该条件谓词返回值为True，否则为False 
13. when inserting then 
14. raise_application_error(-20001,'不能在休息日增加雇员信息！'); 
15. -- 当触发事件是Update操作时，该条件谓词返回值为True，否则为False 
16. when updating then 
17. raise_application_error(-20002,'不能在休息日修改雇员信息！'); 
18. -- 当触发事件是Delete操作时，该条件谓词返回值为True，否则为False 
19. when deleting then 
20. raise_application_error(-20003,'不能在休息日删除雇员信息！'); 
21. end case; 
22. end if; 
23.end; 
24./ 
-- 语句级触发器
-- 禁止员工在休息日改变雇员信息
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操作时，该条件谓词返回值为True，否则为False
when inserting then
raise_application_error(-20001,'不能在休息日增加雇员信息！');
-- 当触发事件是Update操作时，该条件谓词返回值为True，否则为False
when updating then
raise_application_error(-20002,'不能在休息日修改雇员信息！');
-- 当触发事件是Delete操作时，该条件谓词返回值为True，否则为False
when deleting then
raise_application_error(-20003,'不能在休息日删除雇员信息！');
end case;
end if; 
end;
/




Sql代码 
1.-- 限制员工的工资不能超过当前的最高工资 
2.create or replace trigger tr_emp_salary before 
3. -- update of 后面指定在那些列被改变时才调用这个触器，如果不加of 默认是所有列 
4. update of salary on employee 
5. -- 指定该触发器为行级触发器 
6. for each row 
7.-- 如果要定义变量，则只能在Declare中定义 
8.declare 
9. maxSalary number(10,2); 
10.begin 
11. select max(salary) into maxSalary from employee; 
12. if :new.salary > maxSalary then 
13. raise_application_error(-20010,'员工工资超出工资上限！'); 
14. end if; 
15.end; 
16./ 
-- 限制员工的工资不能超过当前的最高工资
create or replace trigger tr_emp_salary before
-- update of 后面指定在那些列被改变时才调用这个触器，如果不加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代码 
1.-- 设置员工的工资不能低于原工资，但也不能高出原工资的20% 
2.create or replace trigger tr_emp_say before update of salary 
3. on employee for each row 
4. -- 设置执行触发器的条件 
5. when (new.salary < old.salary or new.salary > old.salary*1.2) 
6. begin 
7. raise_application_error(-20011,'员工的不能降薪，但工资升幅不能超过20%！'); 
8.end; 
-- 设置员工的工资不能低于原工资，但也不能高出原工资的20%
create or replace trigger tr_emp_say before update of salary
on employee for each row
-- 设置执行触发器的条件
when (new.salary < old.salary or new.salary > old.salary*1.2)
begin
raise_application_error(-20011,'员工的不能降薪，但工资升幅不能超过20%！'); 
end; 


在CMD中执行：audit insert,update,delete on employee by access; 
可以设置Employee表的审计选项，如果在Employee表上执行了Insert、 
Update和Delete操作，Oracle会将关于SQL操作的信息(用户、时间等) 
写入数据字典中，但使用数据库审计只能审计SQL操作，而不能记载数据变化 


Instead of 触发器： 
它是DML触发器的替代品，控制对视图的操作，它可以使不能更新的视图变为可更新， 
以及覆盖可更新的视图的行为 
注意： 
Instead of 选项只适用于视图 
当基于视图建立触发器时，不能指定Before和After选项 
在建立视图时没有指定with check option选项 
当建立Instead of触发器时，必须指定for each row选项 


Sql代码 
1.-- 创建用户表 
2.-- drop table users; 
3.create table users( 
4. userId int not null, 
5. username varchar(20) not null, 
6. password varchar2(50) not null, 
7. logintime date not null 
8.) 
9.-- 向用户表中插入数据 
10.insert into users values(1,'user1',11111,sysdate); 
11.insert into users values(2,'user2',22222,sysdate); 
12.insert into users values(3,'user3',33333,sysdate); 
13.insert into users values(4,'user4',44444,sysdate); 
14.insert into users values(5,'user5',55555,sysdate); 
15.select * from users; 
16.-- 建立复杂视图 
17.-- drop view emp_users; 
18.create or replace view emp_users as 
19.select distinct e.empno,e.name,u.username from employee e,users u where e.empno = u.userId; 
20. 
21.select * from emp_users; 
22.-- drop view tr_instead_of_emp_users; 
23.create or replace trigger tr_instead_of_emp_users 
24.instead of insert on emp_users for each row 
25.declare 
26. i_temp int; 
27.begin 
28. -- 同时向两张表中插入数据 
29. select count(*) into i_temp from employee where empno = :new.empno; 
30. if i_temp = 0 then 
31. insert into Employee values(:new.empno,:new.name,'销售经理',1,'2009-01-09',5300,'销售',4); 
32. end if; 
33. 
34. select count(*) into i_temp from users where userId = :new.empno; 
35. if i_temp = 0 then 
36. insert into users values(:new.empno,:new.username,55555,sysdate); 
37. end if; 
38.end; 
39./ 
-- 创建用户表
-- 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);
insert 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,e.name,u.username 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 empno = :new.empno;
if i_temp = 0 then
insert into Employee values(:new.empno,:new.name,'销售经理',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);
end if;
end;
/




Sql代码 
1.-- 测试Instead of 触发器 
2.insert into emp_users values(10,'王五','小李'); 
3.insert into emp_users values(11,'张三','小明'); 
4.select * from emp_users; 
-- 测试Instead of 触发器
insert into emp_users values(10,'王五','小李');
insert into emp_users values(11,'张三','小明');
select * from emp_users;


数据库事件触发器： 
在数据库启动、关闭、用户登录、退出或者Oracle错误发生时，以及执行创建、删除表、索引等DDL语句时激发 
主要用于跟踪数据库活动 
创建打开数据库触发器：after startup 
创建用户登录触发器：after logon 
创建用户退出触发器：before logoff 
创建DDL触发器：after ddl 


Sql代码 
1.-- 建立用户登录的触发器 
2.create or replace trigger tr_logon after logon on database 
3. begin 
4. raise_application_error(-20001,ora_login_user || '用户登录数据库！'); 
5.end; 
6./ 
7.-- 建立用户退出的触发器 
8.create or replace trigger tr_logoff before logoff on database 
9. begin 
10. raise_application_error(-20002,ora_login_user || '用户退出数据库！'); 
11.end; 
12./ 
13.-- 创建DDL日志表 
14.create table ddl_log 
15.( 
16. -- DDL事件 
17. ddl_event varchar2(20), 
18. -- 对应的数据库登录用户名 
19. username varchar2(10), 
20. -- 对应的对象的所有者名 
21. owner varchar2(10), 
22. -- 对应的数据库对象名 
23. objname varchar2(50), 
24. -- 对应的数据库对象类型 
25. objtype varchar2(10), 
26. -- 对应的数据库操作时间 
27. time date 
28.) 
29.-- 创建DDL触发器 
30.create or replace trigger tr_ddl_log 
31.after ddl on database 
32.begin 
33. insert into ddl_log values( 
34. ora_sysevent,ora_login_user,ora_dict_obj_owner, 
35. ora_dict_obj_name,ora_dict_obj_type,sysdate 
36. ); 
37.end; 
38. 
39.select * from ddl_log 
40.-- 测试DDL触发器 
41.create table aa(aid int) 
42.drop table aa 
-- 建立用户登录的触发器
create or replace trigger tr_logon after logon on database
begin
raise_application_error(-20001,ora_login_user || '用户登录数据库！');
end;
/
-- 建立用户退出的触发器
create or replace trigger tr_logoff before logoff on database
begin
raise_application_error(-20002,ora_login_user || '用户退出数据库！');
end;
/
-- 创建DDL日志表
create table ddl_log
(
-- DDL事件
ddl_event varchar2(20),
-- 对应的数据库登录用户名
username varchar2(10),
-- 对应的对象的所有者名
owner varchar2(10),
-- 对应的数据库对象名
objname varchar2(50),
-- 对应的数据库对象类型
objtype varchar2(10),
-- 对应的数据库操作时间
time date
)
-- 创建DDL触发器
create or replace trigger tr_ddl_log
after ddl on database
begin
insert into ddl_log values(
ora_sysevent,ora_login_user,ora_dict_obj_owner,
ora_dict_obj_name,ora_dict_obj_type,sysdate
);
end;


select * from ddl_log
-- 测试DDL触发器
create table aa(aid int)
drop table aa