触发器
语句级触发器，未使用for each row，被触发后只执行一次
create or replace trigger YHStrig1
before insert or update or delete
on td_b_action
begin
insert into td_b_attr_define(attr_code,attr_name)
values (sysdate,'YHS');
end YHStrig1;
update td_b_action set action_name='YHS1111' where action_id in ('100000022','100000023','100000024');
update语句影响了3行
td_b_attr_define里插入一行，触发器内容被执行了一次
select * from td_b_attr_define
每一个IF里面可以是作更多的操作
create or replace trigger YHStrig1
before insert or update or delete
on td_b_action
declare
Action varchar2(20);
begin
if inserting then
Action :='INSERT';
elsif updating then
Action :='UPDATE';
insert into td_b_attr_define(attr_code,attr_name)
values (sysdate,'YHS_PP');
elsif deleting then
Action :='DELETE';
end if;
insert into td_b_attr_define(attr_code,attr_name)
values (sysdate,Action);
end YHStrig1;
update td_b_action set action_name='YHS1111' where action_id in ('100000022','100000023','100000024');
select * from td_b_attr_define
Before，在语句执行前被触发，当要记录操作日志时使用
行级触发器，使用for each row，如果一个DML语句操作影响多行，触发器执行多次
create sequence seq;
create or replace trigger YHStrig2
before insert or update or delete
on td_b_action
for each row
begin
if inserting then
select seq.nextval
into :new.action_id--action_id使用这个seq.nextval的值
from dual;
elsif updating then
insert into td_b_attr_define(attr_code，attr_name)values('111','222HHH');
elsif deleting then
dbms_output.put_line(:old.action_id);--insert对应:new，delete对应:old
end if;
end YHStrig2;
指不指定action_id，它都将是seq.nextval。
insert into td_b_action(action_id，action_name) values('12323','YYYHHH');
update td_b_action set action_name='YHS1111' where action_id in ('100000001','100000002','100000003');
受影响3条，执行3次
delete from td_b_action where action_id in ('1','2','3','4');
Instead of触发器
定义在视图上，通常情况是，视图是由多个表的字段构成的，是不能直接进行增删改的。
用instead of触发器也就可以像操作一般的表一样操作视图，其实是在触发器内部对各个表进行操作的。
create or replace view YHS_views
as
select attr_code,attr_name,action_name
from td_b_action,td_b_attr_define
where td_b_attr_define.attr_value = td_b_action.action_id;
create or replace trigger YHS_INS
instead of
insert on YHS_views
for each row
declare
bianliang td_b_action.action_id%type;
begin
insert into td_b_attr_define(attr_code,attr_name)
values(:new.attr_code,:new.attr_name);
--根据传入的name去查id作为value的值
select action_id
into bianliang
from td_b_action
where action_name=:new.action_name;
update td_b_attr_define
set attr_value=bianliang
where attr_code=:new.attr_code;
end YHS_INS;
--像操作表一样操作视图
insert into YHS_views(attr_code,attr_name,action_name)
values('100','用action_name的ID作value','宝视通资费变更');
select * from YHS_views;
评论