if(counter>=1) then
begin-----根据条件查询
select count(*) into coun from TB_HD where SrcTermID=:new.SrcTermID and DestTermID=:new.DestTermID;if(coun>1) then beginXP_HD_SERVICE(:new.SrcTermID,0,:new.DestTermID,'LOVE',:new.msgContent,'',taskName,v_LinkToNodeName);
end;
eles begin
select ID,FlowName into operationId,taskName from MUTUAL_OPERTION where SpNumber=:new.DestTermID and State = 1;
SELECT SYS_WFS_UNITS.UnitName,SYS_WFS_LINKS.LinkCondExpress, SYS_WFS_LINKS.LinkToNodeName into v_unitename,v_LinkCondExpress,v_LinkToNodeName
FROM SYS_WFS_UNITS, SYS_WFS_LINKS WHERE (SYS_WFS_UNITS.WF_NAME = taskName) AND (SYS_WFS_LINKS.WF_NAME = taskName) AND (SYS_WFS_UNITS.UnitType = 'TFlowWorkBegin') AND (SYS_WFS_UNITS.UnitName(+) = SYS_WFS_LINKS.LinkFromNodeName);insert into TB_HD(SrcTermID,DestTermID,msgContent,receiveTime,OpertionID,TacheName,UnitName)
values(:new.SrcTermID,:new.DestTermID,:new.msgContent,:new.receiveTime,operationId,taskName,v_unitename);SELECT UnitText into returnContent FROM SYS_WFS_UNITS WHERE (WF_NAME =taskName ) AND (UnitName =v_LinkToNodeName);
XP_SendOneMessage(:new.DestTermID,:new.SrcTermID,serviceId,0,0,'1','000000','000000',0,NULL,0,15,returnContent,'',0,0);
end;
end if;
end if;
begin-----根据条件查询
select count(*) into coun from TB_HD where SrcTermID=:new.SrcTermID and DestTermID=:new.DestTermID;if(coun>1) then beginXP_HD_SERVICE(:new.SrcTermID,0,:new.DestTermID,'LOVE',:new.msgContent,'',taskName,v_LinkToNodeName);
end;
eles begin
select ID,FlowName into operationId,taskName from MUTUAL_OPERTION where SpNumber=:new.DestTermID and State = 1;
SELECT SYS_WFS_UNITS.UnitName,SYS_WFS_LINKS.LinkCondExpress, SYS_WFS_LINKS.LinkToNodeName into v_unitename,v_LinkCondExpress,v_LinkToNodeName
FROM SYS_WFS_UNITS, SYS_WFS_LINKS WHERE (SYS_WFS_UNITS.WF_NAME = taskName) AND (SYS_WFS_LINKS.WF_NAME = taskName) AND (SYS_WFS_UNITS.UnitType = 'TFlowWorkBegin') AND (SYS_WFS_UNITS.UnitName(+) = SYS_WFS_LINKS.LinkFromNodeName);insert into TB_HD(SrcTermID,DestTermID,msgContent,receiveTime,OpertionID,TacheName,UnitName)
values(:new.SrcTermID,:new.DestTermID,:new.msgContent,:new.receiveTime,operationId,taskName,v_unitename);SELECT UnitText into returnContent FROM SYS_WFS_UNITS WHERE (WF_NAME =taskName ) AND (UnitName =v_LinkToNodeName);
XP_SendOneMessage(:new.DestTermID,:new.SrcTermID,serviceId,0,0,'1','000000','000000',0,NULL,0,15,returnContent,'',0,0);
end;
end if;
end if;
create or replace trigger XP_HD_MO
after insert on SM_MO
for each row
declare
counter int;
coun int;
operationId int;
taskName varchar(20);
v_unitename varchar (100);
returnContent varchar(140);
v_LinkToNodeName varchar(100);v_LinkCondExpress varchar(100);begin
--------查询MUTUAL_OPERTION表,,返回结果集,如果有值的话代表该业务是已经开通的互动类业务,如果没有的话返回
select count(*) into counter from MUTUAL_OPERTION where SpNumber=:new.DestTermID and State = 1;if(counter>=1) then
begin-----根据条件查询
select count(*) into coun from TB_HD where SrcTermID=:new.SrcTermID and DestTermID=:new.DestTermID;if(coun>1) then begin
XP_HD_SERVICE(:new.SrcTermID,0,:new.DestTermID,'LOVE',:new.msgContent,'',taskName,v_LinkToNodeName,v_LinkCondExpress);
end;
eles begin
select ID,FlowName into operationId,taskName from MUTUAL_OPERTION where SpNumber=:new.DestTermID and State = 1;
SELECT SYS_WFS_UNITS.UnitName,SYS_WFS_LINKS.LinkCondExpress, SYS_WFS_LINKS.LinkToNodeName into v_unitename,v_LinkCondExpress,v_LinkToNodeName
FROM SYS_WFS_UNITS, SYS_WFS_LINKS WHERE (SYS_WFS_UNITS.WF_NAME = taskName) AND (SYS_WFS_LINKS.WF_NAME = taskName) AND (SYS_WFS_UNITS.UnitType = 'TFlowWorkBegin') AND (SYS_WFS_UNITS.UnitName(+) = SYS_WFS_LINKS.LinkFromNodeName);insert into TB_HD(SrcTermID,DestTermID,msgContent,receiveTime,OpertionID,TacheName,UnitName)
values(:new.SrcTermID,:new.DestTermID,:new.msgContent,:new.receiveTime,operationId,taskName,v_unitename);SELECT UnitText into returnContent FROM SYS_WFS_UNITS WHERE (WF_NAME =taskName ) AND (UnitName =v_LinkToNodeName);
XP_SendOneMessage(:new.DestTermID,:new.SrcTermID,serviceId,0,0,'1','000000','000000',0,NULL,0,15,returnContent,'',0,0);
end;
end if;
end if;
end;
至于你那个地方错了,看得眼花,帮不了你
select count(*) into counter from MUTUAL_OPERTION where SpNumber=:new.DestTermID and State = 1;
if(counter<1) then return;
else
begin************************这个地方, 即select count(*) .....这条语句,如果当MUTUAL_OPERTION
没有符合SpNumber=:new.DestTermID and State = 1;的记录时,会引起exception error
最好改成 select nvl(sum(1),-1) .....当没有数据时返回-1, 有时返回正数.try it.中没有符合
after insert on SM_MO
for each row
declare
counter int; <----建议将所有数字类型定义成number;
coun int;
operationId int;
taskName varchar(20);
v_unitename varchar (100);
returnContent varchar(140);
v_LinkToNodeName varchar(100);
lunitname varchar(100);
v_LinkCondExpress varchar(100);begin
select count(*) into counter from MUTUAL_OPERTION where SpNumber=:new.DestTermID and State = 1;
if counter<1 then <---这里原来有括号的,不用.
null; --return; <-----这里改成null; 什么都不处理;
else
begin
select count(*) into coun from TB_HD where SrcTermID=:new.SrcTermID and
DestTermID=:new.DestTermID; if coun>1 then <---这里原来有括号的,不用.
begin
XP_HD_SERVICE
(:new.SrcTermID,0,:new.DestTermID,'LOVE',:new.msgContent,'',
taskName,v_LinkToNodeName);
end;
else --eles <---这里的else写错了
begin
select ID,FlowName into operationId,taskName from MUTUAL_OPERTION
where SpNumber=:new.DestTermID and State = 1;
SELECT SYS_WFS_UNITS.UnitName,SYS_WFS_LINKS.LinkCondExpress,
SYS_WFS_LINKS.LinkToNodeName into v_unitename,v_LinkCondExpress,
v_LinkToNodeName FROM SYS_WFS_UNITS, SYS_WFS_LINKS
WHERE (SYS_WFS_UNITS.WF_NAME = taskName) AND (SYS_WFS_LINKS.WF_NAME =
taskName) AND (SYS_WFS_UNITS.UnitType = 'TFlowWorkBegin') AND
(SYS_WFS_UNITS.UnitName(+) = SYS_WFS_LINKS.LinkFromNodeName); insert into TB_HD
(SrcTermID,DestTermID,msgContent,receiveTime,OpertionID,
TacheName,UnitName)
values
(:new.SrcTermID,:new.DestTermID,:new.msgContent,:new.receiveTime,
operationId,taskName,v_unitename);
SELECT UnitText into returnContent FROM SYS_WFS_UNITS WHERE
(WF_NAME =taskName ) AND (UnitName =v_LinkToNodeName);
XP_SendOneMessage
(:new.DestTermID,:new.SrcTermID,serviceId,0,0,'1','000000',
'000000',0,NULL,0,15,returnContent,'',0,0)
end;
end if;
end ; <------按照你的结构,这里应该少了一个end 和上面的begin 相对应.加一end
end if;
end;逻辑上的错误你自己查一下了.还有.触发器中执行的过程,不能对触发该表进行操作,因为使用行级触发.