在INSERT触发器写了如下语句(该语句主要是在ICMrpResult新增记录时,系统自动把seorderentry表中fdate和FEntrySelfS0163字段中的信息分别更新至ICMrpResult表中FHeadSelfJ0544和FHeadSelfJ0545字段中):
update ICMrpResult set
FHeadSelfJ0544=e1.fdate,
FHeadSelfJ0545=e1.FEntrySelfS0163
from INSERTED v1 inner join seorderentry e1
on ((e1.FInterID = v1.FOrgSaleInterID) and (e1.fitemid=v1.fitemid) and
(e1.fentryid=v1.FOrgEntyrID) ) left join seorder u1 on (u1.FInterID = v1.FOrgSaleInterID) and (e1.finterid=u1.finterid)
inner join ICMrpResult on (ICMrpResult.finterid=v1.finterid)但现在的问题时,通过程序界面在ICMrpResult表中新增记录时,触发器能把seorderentry表中fdate和FEntrySelfS0163字段中的信息分别更新至ICMrpResult表中FHeadSelfJ0544和FHeadSelfJ0545字段中
但是当在程序界面修改ICMrpResult表中的记录时,FHeadSelfJ0544和FHeadSelfJ0545字段内容不能更新,
各位大虾,我没有写UPDATE触发器,只是在INSERT触发器写了如上语句,怎么修改时不能更新这两个字段的值?
希望能够给出正确的语句
IF OBJECT_ID('tb1') IS NOT NULL DROP TABLE tb1
CREATE TABLE tb1( col1 varchar(20),col2 datetime)IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2
CREATE TABLE tb2( col3 varchar(10),col4 varchar(10),col5 varchar(10),col6 varchar(10))create trigger trig_tb1 on TB1
for insert,update
as
begin
insert tb2(col3,col4,col5,col6)
select
substring(a.col1,1,charindex('-',a.col1+'-')-1), --以“-”为分隔符得到“-”左边字符
substring(a.col1,charindex('-',a.col1+'-')+1,len(a.col1)), -- --以“-”为分隔符得到“-”右边字符 暂时固定长度
CONVERT(varchar(10),getdate(),21), --得到****年**月**日格式
CONVERT(varchar(10),getdate(),108) --得到长日期格式中的时间部分
from inserted a
end
goinsert into tb1 values( 'ip - text','2009-07-22 09:15:45.993')
insert into tb1 values('TCP/IP-iptext',GETDATE())
go
/*
col3 col4 col5 col6
ip text 2009-08-31 10:19:53
TCP/IP iptext 2009-08-31 10:20:24
*/参照!加一个UPDATE
create trigger g_1 on ICMrpResult
for insert,update
as update ICMrpResult set
FHeadSelfJ0544=e1.fdate,
FHeadSelfJ0545=e1.FEntrySelfS0163
from INSERTED v1 inner join seorderentry e1
on ((e1.FInterID = v1.FOrgSaleInterID) and (e1.fitemid=v1.fitemid) and
(e1.fentryid=v1.FOrgEntyrID) ) left join seorder u1 on (u1.FInterID = v1.FOrgSaleInterID)
and (e1.finterid=u1.finterid)
inner join ICMrpResult on (ICMrpResult.finterid=v1.finterid)
go
直接加个update不行吗
如果我在后面加个UPDATE,会不会和我这个表中的UPDATE触发器有冲突吗
如果你的修改后的触发更新操作和插入记录时的更新是一样的话,就没关系
如果更新语句不一样,就像下面这样create trigger g_1 on ICMrpResult
for insert,update
as
if exists(select 1 from inserted)and not exists(select 1 from deleted) --插入更新
update ICMrpResult set
FHeadSelfJ0544=e1.fdate,
FHeadSelfJ0545=e1.FEntrySelfS0163
from INSERTED v1 inner join seorderentry e1
on ((e1.FInterID = v1.FOrgSaleInterID) and (e1.fitemid=v1.fitemid) and
(e1.fentryid=v1.FOrgEntyrID) ) left join seorder u1 on (u1.FInterID = v1.FOrgSaleInterID)
and (e1.finterid=u1.finterid)
inner join ICMrpResult on (ICMrpResult.finterid=v1.finterid)
else --修改更新 ....T-sql
go
按照你的方法,插入后当使用修改界面修改ICMrpResult中 FHeadSelfJ0544, FHeadSelfJ0545字段时,系统仍不能修改!
麻烦各位再帮我想想,如何写触发器啊