各位高手,帮帮俺吧,俺实在是想不通啊....
事情是这样的,我在库里建了两个表t1和t2,表结构为:
t2:
id std_num amount grate
t1:
id std_num class gender
然后我想在更新t1表中的id字段内容的时候更新t2表中相应的id字段,所以我在t1表中写了一个触发器my_trig ,可是我把触发器写完后,手动更新t1表id字段的时候,发现t2表中对应的字段并没有改变,是触发器没有工作,还是触发器写得有些问题,都贴这了,请大家指点。
create my_trig on t1
for update
as
if update(id)
update a set a.id = b.id from t2 a,
inserted b where a.id = b.id
事情是这样的,我在库里建了两个表t1和t2,表结构为:
t2:
id std_num amount grate
t1:
id std_num class gender
然后我想在更新t1表中的id字段内容的时候更新t2表中相应的id字段,所以我在t1表中写了一个触发器my_trig ,可是我把触发器写完后,手动更新t1表id字段的时候,发现t2表中对应的字段并没有改变,是触发器没有工作,还是触发器写得有些问题,都贴这了,请大家指点。
create my_trig on t1
for update
as
if update(id)
update a set a.id = b.id from t2 a,
inserted b where a.id = b.id
for update
as
if update(id)
update a set a.id = b.id from t2 a,
deleted b where a.id = b.id--换成deleted,要不怎么关联得上?
for update
as
if update(id)
update a set a.id = c.id from t2 a,
deleted b,inserted c where a.id = b.id
for update
as
if update(id)
select identity(1,1) as newid, * into #1 from deleted
select identity(1,1) as newid, * into #2 from insertedupdate a set a.id = c.id from t2 a,
#1 b,#2 c where a.id = b.id and b.newid = c.newid
drop #1
drop #2
as
if update(id)
update a set a.id = c.id from t2 a,
deleted b,inserted c where a.id = b.id
--------
能这样写? 应该是drop table #1 吧.
恩,我希望deleted和inserted内部能有关联字段.刚看了下,原来这两个表的表结构和被操作的表结构是一模一样.所以需要我们人为地加标志符字段一一对应这两张逻辑表.
好的~
-----------
这个也需要改成: identity(int) as newid 吧. 呵呵 ,意思写对了,但不仔细啊.
as
if not exists(select * from deleted a ,inserted b where a.id=b.id)
update a set a.id=b.id from t2 a,inserted b where a.id=b.id
我以前用过这个触发器,能够更新数据,但是后来我发现不是更新t2中的id字段,而是t3中的id字段,于是把t2改为t3,却发现触发器不工作了。大家看看这个有问题吗?
CREATE TRIGGER tri_t1 ON [dbo].[t1]
FOR UPDATE
AS
declare @oldID char(10)
declare @newID char(10)
if update(ID)
select @oldID=ID from deleted
select @newID=ID from inserted
update t2 set ID=@newID where ID=@oldID
-----------
还是老问题,因为你换成了t2,当然就不能和inserted 关联上啦. 我刚试了下,我刚才那个也可以处理批量更新的...
对的,但是这样这样只能处理单条update吧.批量的你取不到真确的值的.
for update
as
if update(id)
select identity(int,1,1) as newid, * into #1 from deleted
select identity(int,1,1) as newid, * into #2 from insertedupdate a set a.id = c.id from t2 a,
#1 b,#2 c where a.id = b.id and b.newid = c.newid
drop table #1
drop table #2这下应该没错了吧