问题大致是这样:
当前用户下有alarm_info,TK_EVENT,TK_DEAL_PLAN,TK_DEAL_PLAN_STEP,TK_DEAL_PLAN_ORDER_UNIT,TK_PUBLIC_ISSUE_PLAN_ORDER,TK_Public_Issue_Plan_Order,这几个表之间有外键关联。现在要做的是当表TK_Public_Issue_Plan_Order中某条记录的字段status变为1时,alarm_info的相关记录写到用户datang下的表alarm_to_datang中。
下面是我写的触发器。目前有这样的问题:假设表TK_Public_Issue_Plan_Order中有3条记录,字段status都为0.当把第一条记录中的status变为1时,能正常触发,当再把第2条记录的status变为1时,第2条记录就触发2次了。同理再改第3条时就触发3次了。各个帮忙看下是什么问题.多谢了。create or replace trigger t_TK_Public_Issue_Plan_Order
after insert or update on tk_public_issue_plan_order
for each row
declare
-- local variables here
PRAGMA AUTONOMOUS_TRANSACTION;
begin if(:new.status = 1 and :new.record_type=1 and :old.status<>1)then
insert into datang.alarm_to_datang
select alarm_info.alarm_info_id,alarm_info.road_segment_id,alarm_info.position,alarm_info.start_stub,alarm_info.end_stub,alarm_info.discovery_time,alarm_info.accident_des,alarm_info.event_type_id,alarm_info.direction_id,alarm_info.alarm_level_id,0 from alarm_info,TK_EVENT,TK_DEAL_PLAN,TK_DEAL_PLAN_STEP,TK_DEAL_PLAN_ORDER_UNIT,TK_PUBLIC_ISSUE_PLAN_ORDER
WHERE ALARM_INFO.ALARM_INFO_ID = TK_EVENT.DETAIL_ID AND TK_EVENT.EVENT_ID = TK_DEAL_PLAN.EVENT_ID AND
TK_DEAL_PLAN.DEAL_PLAN_ID = TK_DEAL_PLAN_STEP.DEAL_PLAN_ID
AND TK_DEAL_PLAN_STEP.DEAL_PLAN_STEP_ID = TK_DEAL_PLAN_ORDER_UNIT.DEAL_PLAN_STEP_ID
AND TK_DEAL_PLAN_ORDER_UNIT.DEAL_PLAN_ORDER_UNIT_ID = :new.deal_plan_order_unit_id;
end if; commit;
end t_TK_Public_Issue_Plan_Order;
当前用户下有alarm_info,TK_EVENT,TK_DEAL_PLAN,TK_DEAL_PLAN_STEP,TK_DEAL_PLAN_ORDER_UNIT,TK_PUBLIC_ISSUE_PLAN_ORDER,TK_Public_Issue_Plan_Order,这几个表之间有外键关联。现在要做的是当表TK_Public_Issue_Plan_Order中某条记录的字段status变为1时,alarm_info的相关记录写到用户datang下的表alarm_to_datang中。
下面是我写的触发器。目前有这样的问题:假设表TK_Public_Issue_Plan_Order中有3条记录,字段status都为0.当把第一条记录中的status变为1时,能正常触发,当再把第2条记录的status变为1时,第2条记录就触发2次了。同理再改第3条时就触发3次了。各个帮忙看下是什么问题.多谢了。create or replace trigger t_TK_Public_Issue_Plan_Order
after insert or update on tk_public_issue_plan_order
for each row
declare
-- local variables here
PRAGMA AUTONOMOUS_TRANSACTION;
begin if(:new.status = 1 and :new.record_type=1 and :old.status<>1)then
insert into datang.alarm_to_datang
select alarm_info.alarm_info_id,alarm_info.road_segment_id,alarm_info.position,alarm_info.start_stub,alarm_info.end_stub,alarm_info.discovery_time,alarm_info.accident_des,alarm_info.event_type_id,alarm_info.direction_id,alarm_info.alarm_level_id,0 from alarm_info,TK_EVENT,TK_DEAL_PLAN,TK_DEAL_PLAN_STEP,TK_DEAL_PLAN_ORDER_UNIT,TK_PUBLIC_ISSUE_PLAN_ORDER
WHERE ALARM_INFO.ALARM_INFO_ID = TK_EVENT.DETAIL_ID AND TK_EVENT.EVENT_ID = TK_DEAL_PLAN.EVENT_ID AND
TK_DEAL_PLAN.DEAL_PLAN_ID = TK_DEAL_PLAN_STEP.DEAL_PLAN_ID
AND TK_DEAL_PLAN_STEP.DEAL_PLAN_STEP_ID = TK_DEAL_PLAN_ORDER_UNIT.DEAL_PLAN_STEP_ID
AND TK_DEAL_PLAN_ORDER_UNIT.DEAL_PLAN_ORDER_UNIT_ID = :new.deal_plan_order_unit_id;
end if; commit;
end t_TK_Public_Issue_Plan_Order;
另外在触发器中不需要commit;
PS1楼:楼主触发器采用的是自治事务,因此可以再触发体中commit和rollback的
这根你触发体中的select查询有关啊!如果查询出来多笔,那么insert到larm_to_datang表就是多笔啊。