oracle数据库触发器练习

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代码
— 显示触发器
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;

— 显示触发器
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代码
— 语句级触发器
— 禁止员工在休息日改变雇员信息
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;
/

— 语句级触发器
— 禁止员工在休息日改变雇员信息
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代码
— 限制员工的工资不能超过当前的最高工资
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 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代码
— 设置员工的工资不能低于原工资,但也不能高出原工资的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;

— 设置员工的工资不能低于原工资,但也不能高出原工资的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代码

— 创建用户表
— 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;

— 创建用户表

— 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代码
— 测试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,’张三’,’小明’);
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 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

wkj9pvrhtaaamzihaaf0hhesne643

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部