在Oracle数据库中,触发器的本质就是PL/SQL代码块。触发器按照触发事件类型和对象的不同,可以分为"语句触发器"、“行触发器”、“instead of 触发器”、“系统事件触发器"和"用户事件触发器”。其中"语句触发器"、“行触发器”、"instead of 触发器"这三种触发器一般针对DML操作;"系统事件触发器"侧重于数据库级的动;"用户事件触发器"一般针对DDL操作。
create or replace trigger '触发器名'
before/after insert/update/delete on '数据表名'
begin
'触发器操作';
end;
该语句是创建触发器的基本语句法;其中’before/after’是指在触发器动作之前或之后触发触发器;'insert/update/delete’表示是触发动作;" on ‘数据表名’ “是指将触发器创建在那张收据表中;” ‘触发器操作’ “就是触发器的逻辑操作。
注:每个’触发器操作’后面必须加上分号”;",并且在结束语句"end"后面也一定要添加分号";“否则在创建触发器时,会重新错误(这个错误不是在创建时报错,而是在触发触发器时会报” ORA-04098:触发器无效且未通过重新验证")
alter trigger '触发器名' disable;
该语句可以禁用名为" ‘触发器名’ "的触发器。
alter trigger '触发器名' enable;
该语句可以重新启用名为" ‘触发器名’ "的触发器。
drop trigger '触发器名';
该语句可以删除名为" ‘触发器名’ "的触发器。
语句触发器
语句触发器的作用对象是一张数据表,其触发动作是"insert"、“update”、“delete”,但是"select"表示触发动作;并且,不管这些DML语句执行多少条记录,该触发器都只会执行一次;所以语句触发器的作用级别为’表’级。
单个触发动作
create or replace trigger tri_insert_student
before insert on student
begin
if user != 'ADMIN' then
raise_application_error(-20001,'权限不足,不能向数据表中插入数据');
end if;
end;
该语句在数据表"student"上创建一个触发器,触发动作是"insert",触发逻辑是"当用户不是’ADMIN’时,就不允许向数据表中插入数据"。
多个触发动作
create or replace trigger tri_insert_student
before insert or update or delete on student
begin
if user != 'ADMIN' then
raise_application_error(-20001,'权限不足,不能向数据表中插入数据');
end if;
end;
该语句在数据表"student"上创建一个触发器,触发动作是"insert"、“update”、“delete”,触发逻辑是"当用户不是’ADMIN’时,就不允许向数据表中插入、更新、删除数据"。
触发器谓词
在Oracle数据库中,触发器谓词共有3种:‘inserting’、‘updating’和’deleting’。
‘inserting’:如果触发语句是’insert’语句,则为’true’,否则为’false’;
‘updating’:如果触发语句是’update’语句,则为’true’,否则为’false’;
‘deleting’:如果触发语句是’delete语句,则为’true’,否则为’false’;
create table student_log
( user_name varchar2(25),
action varchar2(10),
log_time date
)
create or replace trigger tri_student_log
before insert or update or delete on student
begin
if inserting then
insert into student_log values(user,'insert',sysdate);
end if;
if updating then
insert into student_log values(user,'update',sysdate);
end if;
if deleting then
insert into student_log values(user,'delete',sysdate);
end if;
end;
该语句可以根据对数据表"student"进行的某种操作,在对应的数据表"student_log"中插入一条对应的操作信息。
行触发器
行触发器的作用对象是数据表中的每一条数据,他常用于保存历史数据,即当数据表中的数据发生变化时,行触发器可以保存历史数据。行触发器的创建语法与语句触发器的创建语法类似,只是添加了’for each row’选项,该选项是行触发器的标志。
create table student
( id number,
name varchar2(25),
sex varchar2(5),
age number,
status varchar2(25)
)
create table student_history
( id number,
name varchar2(25),
sex varchar2(5),
age number,
status varchar2(25)
)
create or replace trigger tr_student_history
before update or delete on student for each row
begin
insert into student_history values(:old.id, :old.name, :old.sex, :old.age,:old.status);
end;
该语句在数据表"student"上创建一个保存历史数据的触发器(当更改、删除数据时,会将当前数据插入的历史数据表"student_history"中),其中":old.id"代指数据表"student"中的"id"字段,"for each row"表示每操作一条语句,该触发器就触发一次。
行触发器的变量引用
在Oracle数据库中的行触发器用两个变量引用:":old"和":new".
“:old"表示引用以前的记录,”:new"表示引用当前新的记录;并且":old"的触发动作只有"update"和"delete",":new"的触发动作只有"insert"和"update"
create or replace trigger tr_student_upper
before update or insert on student for each row
begin
:new.status := upper(:new.status);
:new.age := 18;
end;
该语句在数据表"student"中创建一个将数据表"student"中字段为"status"的信息自动转换成大写字母,并且会将数据表中的"age"字段修改成18。此外,触发器中修改的字段,只要触发了触发器,无论该触发动作中是否操作该字段,该字段都会执行触发器中的结果。
referencing
在Oracle数据库中,如果不喜欢使用":old"和":new"变量,可以使用"referencing"关键字来自己定义变量名。
create or replace trigger tr_student_insert
before insert
on student
referencing new as new_value
for each row
begin
declare max_id number;
begin
select max(id) into max_id from student;
:new_value.id := max_id + 1;
end;
end;
该语句将是使用"referencing"关键字定义的"new_value"来代替"new";并且该触发器的功能相当于一个序列,即可以自动的实现数据表"student"字段"id"自增的效果。
多个触发器的执行顺序
在Oracle数据库中,当一个动作触发多个触发器时,他们的执行顺序为:先表级再行级;如果是同一级别,就按照创建的时间,从后往前执行。
insert of触发器
在Oracle数据库中,因为不能对视图进行插入或更新操作,所以,insert of触发器就是为了解决这个问题而存在的。
create or replace view vv_total_salary as
select e.employee_id, e.employee_name, sum(s.salary) total_salary from employee e, salary s
where e.employee_id = s.employee_id group by e.employee_id, e.employee_name;
create or replace trigger tr_total_salary
insert of update
on vv_total_salary
begin
declare differ number;
months number;
begin
select count(s.month) into months from salary s
where s.employee_id = :old.employee_id;
differ := (:new.total_salary - :old.total_salary)/months;
update salary set salary = salary + differ where employee_id =
:old.employee_id;
end;
end;
该触发器的会让原数据表的数据发生改变,因为"update salary set salary = salary + differ where employee_id =:old.employee_id"就是对原数据表进行操作。
系统事件触发器
系统事件是指对数据库级别的动作所触发的事件,包括数据库启动、数据库关闭、系统错误等。
create or replace trigger tr_db_log
after startup on database
begin
insert into db_log values(user,'startup',sysdate);
end;
该语句创建了一个启动数据库的触发器,此处只能是"after",因为,只有开启数据库之后,才能向日志中插入数据。
create or replace trigger tr_db_log
before shutdown on database
begin
insert into db_log values(user,'shutdown',sysdate);
end;
该语句创建了一个关闭数据库的触发器,此处只能是"before",因为,只有在关闭数据库之前,才能向日志中插入数据。