CREATE OR REPLACE TRIGGER "TRG_WF_SAFE_POLICY" before insert
on WF_SAFE_POLICY for each row
declare
s integer;
begin
if(:new.PKID is null) then
select SEQ_WF_SAFE_POLICY_ID.NEXTVAL INTO :new.PKID from dual;
else
select SEQ_WF_SAFE_POLICY_ID.NEXTVAL INTO s from dual;
end if;
end;
CREATE OR REPLACE TRIGGER synchroLockAccount //synchroLockAccount 触发器名称
AFTER UPDATE ON aims_account // 当aims_account表变更之后
FOR EACH ROW //指定创建的是行级触发器,若没有该子句则创建的是语句级触发器
declare
temp_a number(15,2) ;
temp_b int;
BEGIN
if :OLD.ballimitflag = '2' then
select count(*) into temp_b from aims_account_amountlock l where l.accountid = :OLD.accountid and l.ballimitflag='2';
if temp_b >0 then
update aims_account_amountlock lc set lc.lock_amount = :new.account_balance where lc.accountid= :OLD.accountid;
end if;
end if;
END;
创建触发器:将对student表的操作都记录到stu_log表中(update of 用于指定一个或多个字段,指定字段被更新时才会触发触发器)
create or replace trigger modify_stu
after insert or delete or update of stu_name
on student
for each row
begin
if inserting then
insert into stu_log values(1,'insert',sysdate,:new.stu_name);
elsif deleting then
insert into stu_log values(2,'delete',sysdate,:old.stu_name);
elsif updating then
insert into stu_log values(3,'update_old',sysdate,:old.stu_name);
insert into stu_log values(4,'update_new',sysdate,:new.stu_name);
end if;
end;
语句级触发器(before触发器):用来控制对表的修改
create or replace trigger modify_stu
before insert or update or delete on student
begin
if deleting then
raise_application_error(-20001,'该表不允许删除数据');
elsif updating then
raise_application_error(-20002,'该表不允许修改数据');
elsif inserting then
raise_application_error(-20003,'该表不允许插入数据');
end if;
end;