块分匿名块,命名块(1子程序含过程和函数,2触发器)
触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的DML语句(Insert,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。
语法:
CREATE OR REPLACE TRIGGER 触发器名称
{AFTER|BEFORE } -- 指定触发时机,在什么事件之前或之后执行
{INSERT OR DELETE OR UPDATE} -- 指定触发器事件
ON 表名 --指定所监控的表
{FOR EACH ROW|FOR EACH STATEMENT} -- 指定触发器次数
FOR EACH ROW 行级触发,示例:delete from t1,删除1000行,则执行1000次(一行一次)
FOR EACH STATEMENT 语句级触发,示例: delete from t1,删除1000行,则执行1次(一句一次) ,可以不写
when --触发条件 可以不写
declare --自动调用运行,后边可以写变量
--代码;
:new 行变量:保存事件发生时新数据所在行,只有insert事件和update事件才有新数据
:old 行变量:保存事件发生时旧数据所在行,只有delete事件和update事件才有旧数据
三个条件谓词 inserting 在insert 时候返回true ,后边同理updatding对应update deleting 对应delete
END;
DML触发器应用
1、控制数据安全(数据范围内有效) 2、实现数据统计(将变化写入新表) 3、数据完整性 (满足行业规则) 4、参照完整性(可以改变受影响的其他表) 5、复制数据
Instead of 触发器:
它是DML触发器的替代品,控制对视图的操作,它可以使不能更新的视图变为可更新, 以及覆盖可更新的视图的行为
注意:
Instead of 选项只适用于视图
当基于视图建立触发器时,不能指定Before和After选项
在建立视图时没有指定with check option选项
当建立Instead of触发器时,必须指定for each row选项
管理触发器
-- 显示触发器 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;
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;
-- 触发器 主键自动增长
create sequence seq_dept_pkid
increment by 10 start with 50;
create or replace trigger tri_dept_pkid
before
insert on dept
for each row
declare
begin
select seq_dept_pkid.nextval
into :new.deptno from dual;--select 没有old只有new
end;
insert into dept(dept.deptno,dname,loc) values(1,'jj','jj');
数据库事件触发器(只能SYSDBA)
在数据库启动、关闭、用户登录、退出或者Oracle错误发生时,以及执行创建、删除表、索引等DDL语句时激发
主要用于跟踪数据库活动
创建打开数据库触发器:after startup
创建用户登录触发器:after logon
创建用户退出触发器:before logoff
创建DDL触发器:after ddl
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;
解锁用户 alter user scott account unlock;commit;conn scott/tiger再输入新密码