我们为什么用触发器呢?
下面摘自部分官方文档:Automatically generate virtual column values( 自动生成虚拟列值 )
Log events( 日志事件 )Gather statistics on table access( 收集统计数据表的访问 )
Modify table data when DML statements are issued against views( 修改表数据的时候DML语句发表反对的意见 )
Enforce referential integrity when child and parent tables are on different nodes of ( 强制引用完整性时,孩子和家长的表在不同的节点 )
a distributed database 一个分布式数据库 Publish information about database events, user events, and SQL statements to ( 发布信息数据库事件,用户事件,和SQL语句 )
subscribing applications( 订阅应用程序 )
Prevent DML operations on a table after regular business hours( 防止DML操作一个正在正常生产环境时间段的表)
下面简介介绍几种触发器(trigger涉及的内容较多,了解更多详细参考官方文档):
DML触发器 :当发出UPDATE、INSERT、DELETE命令就可以触发已定义好的DML触发器( 不能对sys用户创建的表建触发器)
语法:
create or replace trigger trigger_name
after|before insert|update|delete
on table_name
for each row
System触发器 (database,ddl) :(文档上概括为system触发器)
当发出CREATE、ALTER、DROP、TRUNCATE命令时会触发已定义好的DDL触发器,这种触发器可以用来监控某个用户或 整个数据库的所有对象的结构变化
语法:
create or replace trigger trigger_name
before|after startup|shutdown|logon|logoff
on database
database 事件触发器:当STARTUP、SHUTDOWN、LOGON、LOGOFF数据库时就会触发DB事件触发器,这种触发器可以用来监控数据库什么时 候关闭/打,或者用户的LOGON/LOGOFF数据库情况
语法:
create or replace trigger trigger_name
before|after startup|shutdown|logon|logoff
on database
下面摘自部分官方文档:Automatically generate virtual column values( 自动生成虚拟列值 )
Log events( 日志事件 )Gather statistics on table access( 收集统计数据表的访问 )
Modify table data when DML statements are issued against views( 修改表数据的时候DML语句发表反对的意见 )
Enforce referential integrity when child and parent tables are on different nodes of ( 强制引用完整性时,孩子和家长的表在不同的节点 )
a distributed database 一个分布式数据库 Publish information about database events, user events, and SQL statements to ( 发布信息数据库事件,用户事件,和SQL语句 )
subscribing applications( 订阅应用程序 )
Prevent DML operations on a table after regular business hours( 防止DML操作一个正在正常生产环境时间段的表)
Prevent invalid transactions(防止非法的事务)
Enforce complex business or referential integrity rules that you cannot define with constraints (
执行复杂的业务或引用完整性规则,你不能定义
约束
)
下面简介介绍几种触发器(trigger涉及的内容较多,了解更多详细参考官方文档):
DML触发器 :当发出UPDATE、INSERT、DELETE命令就可以触发已定义好的DML触发器( 不能对sys用户创建的表建触发器)
语法:
create or replace trigger trigger_name
after|before insert|update|delete
on table_name
for each row
System触发器 (database,ddl) :(文档上概括为system触发器)
当发出CREATE、ALTER、DROP、TRUNCATE命令时会触发已定义好的DDL触发器,这种触发器可以用来监控某个用户或 整个数据库的所有对象的结构变化
语法:
create or replace trigger trigger_name
before|after startup|shutdown|logon|logoff
on database
database 事件触发器:当STARTUP、SHUTDOWN、LOGON、LOGOFF数据库时就会触发DB事件触发器,这种触发器可以用来监控数据库什么时 候关闭/打,或者用户的LOGON/LOGOFF数据库情况
语法:
create or replace trigger trigger_name
before|after startup|shutdown|logon|logoff
on database
instead-of触发器(视图):当向一个由多个表联接成的视图作DML操作时,一般情况下是不允许的,这时候就可以用Instead-of触发器来解决这种问题(在触发器写代码分别对各表作相应DML操作),语法是这样的: create or replace trigger trigger_name instead of insert|update|delete on view_name for each row 下面以试验说明每一种触发器: SQL> select * from v$version where rownum=1; BANNER -------------------------------------------------------------------------------- Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production SQL> show user; USER 为 "HR"DML触发器:禁止用CREATE、ALTER、DROP、TRUNCATE命令操作HR用户的对象
SQL> create table t_trigger(username varchar2(10),ip varchar2(10),time date,term inal varchar2(10),event varchar2(10),id number(10),name varchar2(10));
表已创建。
create or replace trigger tirgger_t
after insert on t for each row declare -- local variables here begin insert into t_trigger values(sys.login_user,sys_context('userenv','ip_address'),sysdate,sys_context('userenv','terminal'),'insert', :new.id,:new.name);(这里记录的是插入后的值,如果换成:old.id,则此列值为空
end tirgger_t; 此时我们插入数据验证:
SQL> insert into t values(0,'os'); 已创建 1 行。 SQL> set linesize 1000; SQL> set pagesize 1000; SQL> select * from t_trigger;
USERNAME IP TIME TER MINAL EVENT ID NAME -------------------- ---------------------------------------- -------------- --- ------------------------------------- -------------------- ---------- ---------- ---------- HR 170.12.15.20 19-4月 -15 PC2 01409141201 insert 0 os
注意::new,:old不能用在表级触发器中
在pl/sql中,提供了inseting,updating,deleting谓词用作流程语句中。
上述触发器如果写成一下形式:
create or replace trigger tirgger_t after insert on t -- for each row 注释此行 declare -- local variables here begin insert into t_trigger values(sys.login_user,sys_context('userenv','ip_address'),sysdate,sys_context('userenv','terminal'),'insert', null,null); end tirgger_t; 则只记录插入的第一行,for ecah row 失效。 System触发器:(不能基于表或视图)
ddl触发器:
create or replace trigger t_tirgger_ddl after ddl on hr.schema =>对于ddl触发器,可以直接写ddl,系统会识别何操作 declare -- local variables here begin insert into t_trigger values(sys.login_user,sys_context('userenv','ip_address'),sysdate,sys_context('userenv','terminal'),sys.sysevent, null,null); end t_tirgger_ddl;
for each row 不能用于对象触发器 对于schema,很多初学者很困惑,不知道这个是什么概念,这个逻辑概念很重要,指用户具有所有对象的集合。
SQL> alter table t add address varchar2(20); 表已更改。 SQL> select * from t_trigger; USERNAME IP TIME TERMINAL EVENT ID NAME -------------------- -------------------- -------------------- ----------------- --- -------------------- ---------- -------------------- HR170.12.15.20 19-4月 -15 PC201409141201ALTER
db触发器:当STARTUP、SHUTDOWN、LOGON、LOGOFF数据库时就会触发DB事件触发器,这种触发器可以用来监控数据库什么时候关闭或打开,或者用户的LOGON/LOGOFF数据库情况(shutdown类型要用关键字before,startup用after)
create or replace trigger t_tirgger_db after logon on hr.schema declare -- local variables here begin insert into t_trigger values(sys.login_user,sys_context('userenv','ip_address'),sysdate,sys_context('userenv','terminal'),sys.sysevent, null,null); end t_tirgger_db; 重新连接:
SQL> select * from t_trigger; USERNAME IP TIME -------------------- ---------------------------------------- -------------- TERMINAL EVENT ID ---------------------------------------- -------------------- ---------- NAME -------------------- HR170.12.15.20LOGON 19-4月 -15 PC201409141201同理也可以创建:
create or replace trigger t_tirgger_db after logoff on hr.schema declare -- local variables here begin insert into t_trigger values(sys.login_user,sys_context('userenv','ip_address'),sysdate,sys_context('userenv','terminal'),sys.sysevent, null,null); end t_tirgger_db;instead-of触发器:
SQL> create view t_view as select name,id from t; 视图已创建。 create or replace trigger t_tirgger_view instead of insert on t_view for each row declare -- local variables here begin insert into t_trigger values(sys.login_user,sys_context('userenv','ip_address'),sysdate,sys_context('userenv','terminal'),sys.sysevent, :new.id,:new.name); end t_tirgger_view;SQL> insert into t_view values('0',0);已创建 1 行。SQL> select * from t_trigger; USERNAME IP TIME -------------------- ---------------------------------------- -------------- TERMINAL EVENT ID ---------------------------------------- -------------------- ---------- NAME -------------------- HRDROP TRIGGER trigger_name;当然你要有相关权限,在第三方工具中操作更加方便,如(pl/sql developer)170.12.15.200 0删除触发器比较简单:19-4月 -15 PC201409141201