1、创建触发器：
--倘若同一个业务的某种规则黄牌的置牌时间小于预警的置牌时间，
--则交换他们的置牌时间。
create or replace trigger after_punish_update_time
after insert on winf_sv_punish
for each row
declare
param_id varchar2(100);--置牌ID
param_breakRule varchar2(100);--违反规则
param_punishResult varchar2(2);--置牌结果（预警，异常，黄牌，红牌）
param_sblsh varchar2(100);--申办流水号
param_yuJingPunishId varchar2(2);--置牌预警的ID
param_smallDate date;--预警的置牌时间
param_bigDate date;--黄牌的置牌时间
begin
param_id:=:new.id;
param_breakRule:=:new.break_rule;
param_punishResult:=:new.punish_result;
param_sblsh:=:new.sblsh;
param_bigDate:=:new.break_time;
--查询出该业务的某个规则的预警ID,预警置牌时间
select p.id,p.break_time into param_yuJingPunishId,param_smallDate from winf_sv_punish p where p.break_rule =(
select r.id from winf_sv_rule r
left join winf_sv_supervisepoint sp on r.supervise_point=SP.ID
where r.supervise_point=(
select r.supervise_point from winf_sv_rule r where r.id=param_breakRule
) and r.rule_punish=‘1‘) and p.sblsh=param_sblsh and p.punish_result=‘1‘;
--规则置牌结果为黄牌
if param_punishResult=‘2‘ and param_bigDate<param_smallDate then
--定义游标查出该部门的核查负责人，若有则发送（状态为0），没有则暂存到待交办任务中（状态为-1）
dbms_output.put_line(‘申办流水号：‘||param_sblsh||‘黄牌ID‘||param_id||‘预警ID‘||param_yuJingPunishId);
--交换置牌时间
update winf_sv_punish p1 set p1.break_time=param_smallDate where p1.id=param_id;
update winf_sv_punish p2 set p2.break_time=param_bigDate where p2.id=param_yuJingPunishId;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN null;
end after_punish_update_time;
2、执行测试语句，报错：
-- Created on 2014/10/16 by LAIWENHUA
declare
-- Local variables here
i integer;
begin
-- Test statements here
insert into WINF_SV_PUNISH
(id,
BREAK_RULE,
BREAK_TIME,
SBLSH,
PUNISH_LEVEL,
PUNISH_RESULT,
OPERATE_TYPE,
PUNISH_USERID,
PUNISH_EXPLAIN,
ISHANDLE,
punish_type,
NODE_ID,
send_status,
send_lead_status,
IS_CONFIRM,
CONFIRM_TIME)
values
(‘81af91db3c1b49a6a0f62b522275007a‘,
‘2f105e0797f84ea5b0cceb6a3f43b7fa‘,
to_date(‘2014-10-16 23:07:50‘, ‘yyyy-mm-dd hh24:mi:ss‘),
‘YC-20141011009‘,
‘1‘,
‘2‘,
‘2‘,
‘3d12f197c5a04f2a8617d591acbf6ea9‘,
‘您所负责的业务申请歇业烟草专卖零售许可证,‘,
‘2‘,
‘1‘,
‘E939179C76C74BE5B29ECA958A3CD67E‘,
‘2‘,
‘2‘,
‘1‘,
to_date(‘2014-10-16 23:07:50‘, ‘yyyy-mm-dd hh24:mi:ss‘));
--(String)
end;
3、解决方法：加上自治事务即可。
在declare后加上PRAGMA AUTONOMOUS_TRANSACTION;
表 XXX 发生了变化 触发器不能读它
原文：http://7883830.blog.51cto.com/7873830/1564969
