请大家帮忙修改一下DB2的触发器，有问题
先附上SQL的触发器（无问题），逻辑可参考该部分CREATEtrigger Send_LT
on tbl_smsendtask instead of insert
as
declare @splitchar nvarchar(10)
declare @dest varchar(2000)
declare @l int
declare @length integer
declare @destaddr varchar(21)
declare @orgaddr varchar(21)
declare @msgcontent varchar(2000)
declare @Sendtime datetime
select @dest=Destaddr,@length=len(destaddr),@orgaddr=orgaddr,@msgcontent=sm_content,@Sendtime=sendtime from inserted
declare @s int
declare @s1 int
set @l = 0
set @s1=len(@msgcontent)
set @splitchar=' '
set @s = charindex(@splitchar, @dest, @l)
while @l <= len(@dest)
begin
if @s = 0 set @s = len(@dest) + 1
set @destaddr = substring(@dest, @l, @s - @l)
set @l = @s + 1
set @s = charindex(@splitchar, @dest, @l)if ltrim(rtrim(@destaddr)) =' ' continue
if (substring(@destaddr,1,3)='134') or (substring(@destaddr,1,3)='135') or (substring(@destaddr,1,3)='136') or (substring
(@destaddr,1,3)='137') or (substring(@destaddr,1,3)='138') or (substring(@destaddr,1,3)='139') or (substring(@destaddr,1,3)
='150') or (substring(@destaddr,1,3)='158') or (substring(@destaddr,1,3)='159')or (substring(@destaddr,1,3)='151')
or(substring(@destaddr,1,3)='157')or (substring(@destaddr,1,3)='188')or (substring(@destaddr,1,3)='187')or (substring
(@destaddr,1,3)='152') or (substring(@destaddr,1,3)='147')or(substring(@destaddr,1,3)='182')or (right(@destaddr,4)='.txt')
begin
insert into tbl_smsendtask(creatorid,smsendednum,operationtype,suboperationtype,sendtype,orgaddr,destaddr,sm_content,sendtime,needstatereport,serviceid,feetype,feecode,smtype,messageid,destaddrtype,subtime,taskstatus,sendlevel,sendstate,trytimes,count,successid)
select creatorid,smsendednum,operationtype,suboperationtype,sendtype,orgaddr,@destaddr,sm_content,sendtime,needstatereport,serviceid,feetype,feecode,smtype,messageid,destaddrtype,subtime,taskstatus,sendlevel,sendstate,trytimes,count,successid
from inserted
end
else
begin
insert into sendLT(phone,srctermid,message,Sendtime)
select @destaddr,@orgaddr,@msgcontent,@Sendtime
from inserted
end
if (right(@destaddr,4)='.txt')
begin
begin tran OK
insert into sendLT(phone,srctermid,message,Sendtime)
select @destaddr,@orgaddr,@msgcontent,@Sendtime
from inserted
commit tran OK
end
end
下面是一个朋友帮忙翻译过来的DB2的，有问题，另外在触发器执行完成后，需要调用shell进行文件复制，这部分不明白该怎么写，请高手帮忙修改一下。
CREATEtrigger Send_LT
instead of insert
on tbl_smsendtask
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
declare splitchar nvarchar(10) ；
declare dest varchar(2000)；
declare l int；
declare length int;
declare destaddr varchar(21)；
declare orgaddr varchar(21)；
declare msgcontent varchar(2000)；
declare Sendtime datetime；
declare s int ;
declare s1 int;
declare v_sql varchar(2000);
set dest=N.Destaddr;
set length=length(N.destaddr);
set orgaddr=N.orgaddr;
set msgcontent=N.sm_content;
set Sendtime=N.sendtime
set l = 0;
set s1=length(@msgcontent);
set splitchar=' ';
set s = charindex(splitchar, dest, l);
while l <= length(dest) do
if s = 0 then
set s = length(dest) + 1 ;
end if;
set destaddr = substr(dest, l, s - l) ;
set l = s + 1;
set s = locate(splitchar, dest,1);
--if ltrim(rtrim(destaddr)) =' ' continue --这句是多余的
if substr(destaddr,1,3) IN ('134','135','136','137','138','139','150','158','159','151','157','188','187','152','147','182') or (right(rtrim(destaddr),4)='.txt' then
set v_sql='insert into tbl_smsendtask(creatorid,smsendednum,operationtype,suboperationtype,sendtype,orgaddr,destaddr,sm_content,sendtime,needstatereport,serviceid,feetype,feecode,smtype,messageid,destaddrtype,subtime,taskstatus,sendlevel,sendstate,trytimes,count,successid)
values(N.creatorid,N.smsendednum,N.operationtype,N.suboperationtype,N.sendtype,N.orgaddr,'''||destaddr||''',N.sm_content,N.sendtime,N.needstatereport,N.serviceid,N.feetype,N.feecode,N.smtype,N.messageid,N.destaddrtype,
N.subtime,N.taskstatus,N.sendlevel,N.sendstate,N.trytimes,N.count,N.successid)';
else
set v_sql= 'insert into sendLT(phone,srctermid,message,Sendtime)
values('''||destaddr||''','''||orgaddr||''','''||msgcontent||''','||Sendtime||')';
end if;
PREPARE s1 FROM v_sql;
EXECUTE s1;
if right(rtrim(destaddr),4)='.txt' then
set v_sql='insert into sendLT(phone,srctermid,message,Sendtime)
values('''||destaddr''','''||orgaddr||''','''||msgcontent||''','||Sendtime||')';
PREPARE s1 FROM v_sql;
EXECUTE s1;
这个地方加上一段代码，主要是调用shell，将文件夹home/we里面的destaddr名字的文件，拷贝到home/ww下面，其中destaddr是一个参数
end if;
end while；
END @
感谢大家费神了！！
页:
[1]