定义
触发器是存在于数据库服务器中的一个程序单元，当一个表或者视图发生改变，或者数据库中的某些事件，触发器自动执行；相比于存储过程来说，它不能被用户直接调用，相当于java中的事件监听程序。
分类行级触发器和语句级触发器
行级触发器是基于行的，当一条sql语句执行时，该语句改变了N行，触发器就执行N次；
语句触发器是基于语句的，当一条sql语句执行时，无论该语句改变了多少行的数据，触发器只执行一次，只要SQL语句执行一次，不管是否修改值，触发器都会执行。
Before和after触发器
Before触发器在触发语句执行之前触发操作，After触发器在触发语句执行之后触发操作
复合触发器触发语句执行前(Before statement)
触发语句执行后(After Statement)
每行记录修改前(Before each row)
每行记录修改后(After each row)
instead of触发器
有的视图，我们不能直接对其进行更新操作，但是我们可以再这种视图上建立触发器，利用触发器对视图的基表进行更新操作，这种类型的触发器就叫做“INSTEAD OF触发器”。
Notice：只能是行触发器;只能应用于视图或者对象视图；
系统级触发器
系统事件触发器是基于数据库系统的触发器，系统事件触发器与表、视图没有关系。系统事件包括数据库启动、关闭、服务器错误、数据库角色改变等。当这些事件发生时，就会触发系统事件触发器。可以通过系统事件触发器实现对数据库的审计。
用户级触发器
用户事件包括用户登录数据库、用户退出数据库、用户执行DDL/DML语句等。当这些事件发生时，会触发用户事件触发器。
触发事件
触发器只能在以下事件中才能被调用：DML操作(INSERT、UPDATE、DELETE)
DDL操作(CREATE、ALTER、DROP)
系统事件(数据库的关闭与启动等)
用户事件(用户的登陆等)
触发器使用语法
创建一般触发器1
2
3
4
5
6
7
8
9CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.]table_name | [schema.]view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ]
[WHEN condition]
PL/SQL_BLOCK | CALL procedure_name;
创建instead of触发器1
2
3
4
5
6
7
8
9CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]
ON [schema.] view_name
[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]
[FOR EACH ROW ] --因为INSTEAD OF触发器只能在行级上触发,所以没有必要指定
[WHEN condition]
PL/SQL_block | CALL procedure_name;
创建系统触发器1
2
3
4
5
6
7
8CREATE OR REPLACE TRIGGER [sachema.]trigger_name
{BEFORE|AFTER}
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.]SCHEMA }
[WHEN condition]
PL/SQL_block | CALL procedure_name;
--1.ddl_event_list：一个或多个DDL 事件，事件间用 OR 分开；
--2.database_event_list：一个或多个数据库事件，事件间用 OR 分开；
创建复合触发器1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24CREATE [OR REPLACE] TRIGGER 触发器名称
trueFOR [INSERT | UPDATE | UPDATE OF 列名称 [,列名称,...] | DELETE] ON 表名称
COMPOUND TRIGGER
[ BEFORE STATEMENT IS -- 语句执行前触发(表级)
[ 声明部分 ; ]
BEGIN
程序主体部分 ;
END BEFORE STATEMENT ; ]
[ BEFORE EACH ROW IS -- 语句执行前触发(行级)
[ 声明部分 ; ]
BEGIN
程序主体部分 ;
END BEFORE EACH ROW ; ]
[ AFTER STATEMENT IS -- 语句执行后触发(表级)
[ 声明部分 ; ]
BEGIN
程序主体部分 ;
END AFTER STATEMENT ; ]
[ AFTER EACH ROW IS -- 语句执行后触发(行级)
[ 声明部分 ; ]
BEGIN
程序主体部分 ;
END AFTER EACH ROW ; ]
END ;
编写触发器
1.表t_user在更新name列时输出更新前的name和更新后的name1
2
3
4
5
6
7create or replace trigger user_insert_trigger
after update of name on t_user
for each row
begin
dbms_output.put_line('old value:' || :old.name);
dbms_output.put_line('new value:' || :new.name);
end;
Notice：执行delete，insert操作语法与之类似，delete只有:old值，insert只有:new值，:old和:new只用于行级触发器中
2.复杂的安全检查1
2
3
4
5
6
7
8CREATE OR REPLACE TRIGGER org_trigger
BEFORE INSERT OR DELETE OR UPDATE
ON t_org
BEGIN
IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN
RAISE_APPLICATION_ERROR(-20001, '不是上班时间，不能修改departments表');
END IF;
END;
3.oracle判断SQL语句类型1
2
3
4
5
6
7
8
9
10
11create or replace trigger t_user_trigger
before insert or update or delete on t_user
begin
if updating then
dbms_output.put_line('updating line');
elsif deleting then
dbms_output.put_line('deleting line');
elsif inserting then
dbms_output.put_line('inserting line');
end if;
end;
4.创建复合触发器1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28create or replace trigger t_user_trigger
for update of name on t_user compound trigger
info1 constant varchar2(200) := 'Before Statement';
info2 constant varchar2(200) := 'Before Each Row';
info3 constant varchar2(200) := 'After Each Row';
info4 constant varchar2(200) := 'After Statement';
before statement is
begin
dbms_output.put_line(info1);
end before statement;
before each row is
begin
dbms_output.put_line(info2);
end before each row;
after each row is
begin
dbms_output.put_line(info3);
end after each row;
after statement is
begin
dbms_output.put_line(info4);
end after statement;
end;
5.创建系统事件触发器1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25//创建日志表
create table t_log
(
id number primary key,
username varchar2(255),
info varchar2(255)
);
//系统登录时触发
create or replace trigger logon_trigger
after LOGON on database
begin
insert into t_log values(t_log_seq.nextval,ora_login_user,ora_sysevent);
end;
//用户离开时触发
create or replace trigger logoff_trigger
before LOGOFF on database
begin
insert into t_log values(t_log_seq.nextval,ora_login_user,ora_sysevent);
end;
//执行DDL操作时触发
CREATE OR REPLACE TRIGGER tr_ddl
AFTER DDL ON SCHEMA
BEGIN
insert into t_log values (t_log_seq.nextval, ora_login_user,ora_sysevent);
END tr_ddl;
参考
913
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
