Oracle之触发器(DML触发器 、替代触发器 、系统触发器 、系统触发器、创建日志记录表、登录触发器、登出触发器)
1.触发器的定义
触发器是指被隐含执行的存储过程,它可以使用PL/SQL进行开发
当发生特定事件(如修改表、创建对象、登录到数据库)时,Oracle会自动执行触发器的相应代码
2.触发器的类型(DML触发器 、替代触发器 、系统触发器 )
1、DML触发器
在对数据库表进行DML(insert,update,delete)操作时触发,并且可以对每行或者语句操作上进行触发。
2、替代触发器
是oracle8专门为进行视图操作的一种触发器
3、系统触发器
对数据库系统事件进行触发,如启动、关闭等
3.注意事项
1. 触发器不接受参数。
2. 一个表上最多可有12个触发器,但同一时间、同一事件、同一类型的触发器只能有一个。并各触发器之间不能有矛盾。
3. 在一个表上的触发器越多,对在该表上的DML操作的性能影响就越大。
4. 触发器最大为32KB。若确实需要,可以先建立过程,然后在触发器中用CALL语句进行调用。
5. 触发器中不能包含事务控制语句(COMMIT,ROLLBACK,SAVEPOINT)。因为触发器是触发语句的一部分,触发语句被提交、回退时,触发器也被提交、回退了。在触发器主体中调用的任何过程、函数,都不能使用事务控制语句。
2.DML触发器练习
1.表头复制
CREATE TABLE dept_log AS SELECT * FROM dept WHERE 1=2;
2.删除示例
-- 删除示例
create or replace trigger tr_del_student
--指定触发时机为删除操作前触发
before delete
on student
--说明创建的是行级触发器
for each row
begin
--将修改前数据插入到日志记录表,要确保SQL可以正常工作
insert into student_log values(:old.stu_id,:old.stu_name,:old.stu_age,:old.stu_sex,:old.stu_class,:old.stu_create_date);
end;
3.修改示例
-- 修改示例
create or replace trigger tr_update_student
--指定触发时机为修改操作后触发
after update
on student
for each row
begin
-- 记录修改后的值
insert into student_log
values(:new.stu_id,:new.stu_name,:new.stu_age,:new.stu_sex,:new.stu_class,:new.stu_create_date);
end;
4.删除触发器
drop trigger trigger_name;
3.登录,登出触发器练习(系统触发器、创建日志记录表、登录触发器、登出触发器)
1.系统触发器
事件 | 允许的时机 | 说明 |
---|---|---|
STARTUP | AFTER | 启动数据库实例之后触发 |
SHUTDOWN | BEFORE | 关闭数据库实例之前触发(非正常关闭不触发) |
SERVERERROR | AFTER | 数据库服务器发生错误之后触发 |
LOGON | AFTER | 成功登录连接到数据库后触发 |
LOGOFF | BEFORE | 开始断开数据库连接之前触发 |
CREATE | BEFORE,AFTER | 在执行CREATE语句创建数据库对象之前、之后触发 |
DROP | BEFORE,AFTER | 在执行DROP语句删除数据库对象之前、之后触发 |
ALTER | BEFORE , AFTER | 在执行ALTER语句更新数据库对象之前、之后触发 |
2.创建日志记录表
-- 创建日志记录表
create table log_event(
log_type varchar2(20),
username varchar2(20),
logonTime date,
logoffTime date
);
3.登录触发器
--登录触发器
create or replace trigger logon_trigger
after logon on database
begin
insert into log_event(log_type,username,logonTime)
values('logon',ora_login_user,sysdate);
end;
4.登出触发器
--登出触发器
create or replace trigger logoff_trigger
before logoff on database
begin
insert into log_event(log_type,username,logoffTime)
values('logoff',ora_login_user,sysdate);
end;