第一：触发器（trigger）
触发器（trigger）是指隐含执行的存储过程procedure，当定义触发器时必须要指定触发事件和触发的操作，通常触发事件包括insert,update,delete语句，触发器实际上就是一个pl/sql(procedure language/Structured Query Language).create trigger来创建触发器；
第二：触发器的作用；
a.允许/限制对表的修改
b.自动派生列，如自增字段
c.强制数据的一致性
d.提供审计和日志记录
e.防止无效的事务处理
f.启动复杂的业务逻辑
第三：触发器的语法；
- create or replace trigger temp_trigger ---定义一个触发器 temp_trigger
- after|before|instead of ---指定触发时机和触发类型
- insert|update|delete ---指定触发事件
- of columns_name on table_name ---of & on 监控的表单和表列
- referencing
- old as old_value
- new as new_value
- for each row|for each statement ---指定触发次数（行和语句）
- begin
- codes
- end;
create or replace trigger temp_trigger ---定义一个触发器 temp_triggerafter|before|instead of ---指定触发时机和触发类型insert|update|delete ---指定触发事件of columns_name on table_name ---of & on 监控的表单和表列referencingold as old_valuenew as new_valuefor each row|for each statement ---指定触发次数（行和语句）begincodesend;
instead of 是一种单独的出发机制，用来管理和执行view类型的数据表单
of &on 监控关键字 of 可以省区,on是必须留下的
第四:例子instance
a:创建一个行触发器 tri
- create or replace trigger tri ---trigger tri
- before insert or update on employee
- referencing old as old_value
- new as new_value
- for each row
- when(new_value.empid<>'14') ---当插入的empid不等于14
- begin
- :new_value.salary:=2001; ---将插入的的salary更改成2001
- end;
create or replace trigger tri ---trigger tribefore insert or update on employee referencing old as old_value new as new_valuefor each rowwhen(new_value.empid<>'14') ---当插入的empid不等于14begin:new_value.salary:=2001; ---将插入的的salary更改成2001end;
触发事件：
- insert into employee values(emp_seq.nextval,'Janney',10,'1'); ---插入一条数据
- update employee set employee.empname='Alex' where employee.empid='12'; --根新empid=15的数据
insert into employee values(emp_seq.nextval,'Janney',10,'1'); ---插入一条数据update employee set employee.empname='Alex' where employee.empid='12'; --根新empid=15的数据
结果：
对比发现，无论你修改或增加数据只要empid!=14,salary都固定在2001。
b:创建一个语句触发器
创建一表单 idiot
- create table idiot(
- i_id varchar2(255) primary key,
- i_name varchar2(255),
- i_add varchar2(255)
- )
create table idiot(i_id varchar2(255) primary key,i_name varchar2(255),i_add varchar2(255))
创建触发器 tri_idiot
- create or replace trigger tri_idiot
- before update or insert on idiot
- begin
- if user not in ('hjd') then
- Raise_application_error(-20001,'You don’t have access to modify this table.');
- end if;
- end;
create or replace trigger tri_idiotbefore update or insert on idiotbeginif user not in ('hjd') thenRaise_application_error(-20001,'You don’t have access to modify this table.');end if;end; 触发事件
- insert into idiot values(idi_seq.nextval,'daniel','武汉');
- insert into idiot values(idi_seq.nextval,'justin','武汉');
- insert into idiot values(idi_seq.nextval,'alex','南昌');
insert into idiot values(idi_seq.nextval,'daniel','武汉');insert into idiot values(idi_seq.nextval,'justin','武汉');insert into idiot values(idi_seq.nextval,'alex','南昌');
结果：
（在非hjd的用户下执行）
- ---复制一张表格 命名为tem_employee 为操作的表格--
- create table tem_employee as select * from employee
---复制一张表格 命名为tem_employee 为操作的表格--create table tem_employee as select * from employee
- --创建一张 tem_employee_log记录变化--
- create table tem_employee_log(
- l_who varchar2(50),
- l_when date
- )
--创建一张 tem_employee_log记录变化--create table tem_employee_log(l_who varchar2(50),l_when date)
- ---增加一column---
- alter table tem_employee_log add(action varchar2(20));
---增加一column---alter table tem_employee_log add(action varchar2(20));
创建触发器 tri
- create or replace trigger tri
- before insert or update or delate on temp_employee
- declare -----声明一变量
- l_action tem_employee_log.action%type;
- begin
- if inserting then l_action:='insert';
- elsif updating then l_action:='update'; --此处用elsif
- elsif deleting then l_action:='delete';
- else raise_application_error(-20001,'You should never ever get this error.');
- end if;
- insert into tem_employee_log values(user,sysdate,l_action);
- end;
create or replace trigger tribefore insert or update or delate on temp_employeedeclare -----声明一变量l_action tem_employee_log.action%type;beginif inserting then l_action:='insert';elsif updating then l_action:='update'; --此处用elsifelsif deleting then l_action:='delete';else raise_application_error(-20001,'You should never ever get this error.');end if;insert into tem_employee_log values(user,sysdate,l_action);end;
触发语句
- delete from tem_employee where tem_employee.empid='4';
delete from tem_employee where tem_employee.empid='4';
结果：
