PLSQL触发器详解-2

before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
6 END;
7 /
8 SQL> INSERT INTO temp VALUES (1); -- insert 1 row
executing temp_air
SQL> INSERT INTO temp SELECT * FROM temp; -- insert 1 row
executing temp_air
BEGIN
Trigger Body written PL/SQL
END;
·Trigger_name
用触发器名来确定表名和触发器类型。PL/SQL运行时错误将产生一个PL/SQL错误信息，涉及触发器名和行数。下面Oracle错误显示了在students表上的AFTER-INSERT行触发器的第5行有一个被0除错误。
select * from employess_log;
5、确定是哪个语句起作用？
即是INSERT/UPDATE/DELETE中的哪一个触发了触发器？
可以在触发器中使用INSERTING / UPDATING / DELETING条件谓词，作判断：
begin
if inserting then
elsif updating then
实例:
create or replace trigger tri_test
after insert or update or delete on test
begin
if updating then
dbms_output.put_line('修改');
elsif deleting then
dbms_output.put_line('删除');
说明：
1、无论是否规定了department_id，对employees表进行insert的时候
2、对employees表的department_id列进行update的时候
3、触发器限制
when (new_value.department_id<>80 )
限制不是必须的。此例表示如果列department_id不等于80的时候，触发器就会执行。
2、触发语句
比如：
表或视图上的DML语句
DDL语句
数据库关闭或启动,startup shutdown等等
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
SQL> INSERT INTO temp SELECT * FROM temp; -- inserts 2 rows
executing temp_air
executing temp_air
SQL>
尽管第三个Insert语句是一条SQL语句，但插入TEMP表中两条记录。许多insert语句插入一条记录，但可以用一条语句插入许多行。
2、行级触发器语法：
CREATE OR REPLACE TRIGGER trigger_name
AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW
[WHEN (Boolean expression)]
DECLARE
Local declarations
触发器类型：
1、语句触发器
2、行触发器
3、INSTEAD OF触发器
4、系统条件触发器
5、用户事件触发器
注释：
before和after：指在事件发生之前或之后激活触发器。
instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。
l_action:=’Insert’;
elsif updating then
l_action:=’Update’;
elsif deleting then
l_action:=’Delete’;
else
raise_application_error(-20001,’You should never ever get this error.’);
例子：
需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的特权。
Create table foo(a number);
Create trigger biud_foo
Before insert or update or delete
On foo
Begin
If user not in (‘DONNY’) then
values(12345,’Chen’,’Donny@hotmail’,sysdate,12);
select *from employees_log
总结：语句级触发器.(语句级触发器对每个DML语句执行一次）是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。
Raise_application_error(-20001,‘You don’t have access to modify this table.’);
End if;
End;
/
即使SYS，SYSTEM用户也不能修改foo表
[试验]
对修改表的时间、人物进行日志记录。
1、建立试验表
create table employees_copy as select * from hr.employees
ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.STUDENTS_AIR", line 5
ORA-04088: error during execution of trigger
'SCOTT.STUDENTS_AIR'
行记数从关键字DECLARE行开始，如果没有DECLARE部分，BEGIN语句是第一行。触发器名称存储在USER_TRIGGERS表的TRIGGER_NAME。触发器名一般由表名、触发器类型、触发事件，语法如下：
elsif inserting then
dbms_output.put_line('插入');
end if;
end;
Oracle触发器详细介绍三--行级触发器
行级触发器
本章介绍行级触发器机制。大部分例子以INSERT出发器给出，行级触发器可从insert update delete语句触发。
1、介绍
values( 12345,’Chen’,’Donny’, sysdate, 12,‘donny@hotmail.com’,60,10000,.25);
select commission_pct from employees where employee_id=12345;
触发器不会通知用户，便改变了用户的输入值。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Declare
L_action employees_log.action%type;
Begin
if inserting then
2、建立日志表
create table employees_log(
who varchar2(30),
when date);
3、在employees_copy表上建立语句触发器，在触发器中填充employees_log表。
Create or replace trigger biud_employee_copy
Before insert or update or delete
On employees_copy
Begin
Insert into employees_log(
Who,when)
Values( user, sysdate);
End;
/
4、测试
update employees_copy set salary= salary*1.1;
SQL> CREATE TABLE temp (N NUMBER);
SQL> CREATE OR REPLACE TRIGGER temp_air
2 AFTER INSERT ON TEMP
3 FOR EACH ROW
4 BEGIN
5 dbms_output.put_line('executing temp_air');
Insert into employees_log(
Who,action,when)
Values( user, l_action,sysdate);
End;
/
3、测试
insert into employees_copy( employee_id, last_name, email, hire_date, job_id)
end;
/
触发器的组成部分：
1、触发器名称
2、触发语句
3、触发器限制
4、触发操作
1、触发器名称
create trigger biufer_employees_department_id
命名习惯：
biufer（before insert update for each row）
employees表名
department_id列名
declare---end：是一个标准的PL/SQL块。
Oracle触发器详细介绍二--语句触发器
1、语句触发器
是在表上或者某些情况下的视图上执行的特定语句或者语句组上的触发器。能够与INSERT、UPDATE、DELETE或者组合上进行关联。但是无论使用什么样的组合，各个语句触发器都只会针对指定语句激活一次。比如，无论update多少行，也只会调用一次update语句触发器。
Oracle触发器详细介绍
Oracle触发器详细介绍一
触发器
是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。
功能：
1、允许/限制对表的修改
2、自动生成派生列，比如自增字段
3、强制数据一致性
4、提供审计和日志记录
5、防止无效的事务处理
6、启用复杂的业务逻辑
开始
create trigger biufer_employees_department_id
elsif deleting then
end if;
end;
if updating(‘COL1’) or updating(‘COL2’) then
------
end if;
[试验]
1、修改日志表
alter table employees_log
add (action varchar2(20));
2、修改触发器，以便记录语句类型。
其中的new_value是代表更新之后的值。
4、触发操作
是触发器的主体
begin
:new_value.commission_pct :=0;
end;
主体很简单，就是将更新后的commission_pctΒιβλιοθήκη Baidu置为0
触发：
insert into employees(employee_id,
last_name,first_name,hire_date,job_id,email,department_id,salary,commission_pct )
referencing：指定新行（即将更新）和旧行（更新前）的其他名称，默认为new和old。
table_or_view_name：指要创建触发器的表或视图的名称。
for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则为语句级触发器。
when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。
触发器是存储在数据库已编译的存储过程，使用的语言是PL/SQL，用编写存储过程一样的方式编写和编译触发器。下面在SQL*PLUS会话中创建和示例一个简单的Insert行级触发器。这个触发器调用DBMS_OUTPUT在每插入一行数据时打印“executing temp_air”
SQL> set feedback off