if(object_id('tgr_public_his_update','TR') is not null) drop trigger tgr_public_his_update go create trigger tgr_public_his_update on GL_MARRIAGE_PUBLIC_HIS UPDATE as IF EXISTS(SELECT * FROM INSERTED ) AND EXISTS(SELECT * FROM DELETED) BEGIN INSERT INTO GL_MARRIAGE_PUBLIC_HIS_INSERTED SELECT * FROM INSERTED END 这是个触发器,当修改GL_MARRIAGE_PUBLIC_HIS(婚姻登记表) 时,把修改的那整行都添加到GL_MARRIAGE_PUBLIC_HIS_INSERTED 表中,由于表中的字段有Image类型,所以我把for用 instead of替代了,现在的问题是 我用sql语句测试时,无法修改GL_MARRIAGE_PUBLIC_HIS这里面的数据,GL_MARRIAGE_PUBLIC_HIS_INSERTED这里面是修改后的数据,求解为什么修改不了GL_MARRIAGE_PUBLIC_HIS里面的数据sql
if(object_id('tgr_public_his_update','TR') is not null)
drop trigger tgr_public_his_update
go
create trigger tgr_public_his_update
on GL_MARRIAGE_PUBLIC_HIS
instead of UPDATE
as
IF EXISTS(SELECT * FROM INSERTED ) AND EXISTS(SELECT * FROM DELETED)
BEGIN
INSERT INTO dbo.GL_MARRIAGE_PUBLIC_HIS_INSERTED SELECT * FROM INSERTED
END
INSTEAD OF UPDATE
as
BEGIN
INSERT INTO GL_MARRIAGE_PUBLIC_HIS_INSERTED(列名称1,列名称2,...)
SELECT * FROM INSERTED
END 跟早上是同一个问题/?
吃饭赶回来,instead of 触发器截断了后续的处理。 lz可以在instead of 触发器中编写相应的更新逻辑。
go
create table [TB] (col1 INT PRIMARY KEY ,col2 int,col3 image)
insert into [TB]
select 1,2,null union all
select 2,4,null union all
select 3,5,NULLcreate table [testB] (col1 int,col2 int,col3 image)select * from [Tb]
create TRIGGER Trg_TB ON TB
INSTEAD OF UPDATE
AS
begin
UPDATE dbo.TB --重新实现后续的处理逻辑
SET col2 = T.col2,col3=T.col3
FROM dbo.TB
INNER JOIN INSERTED T ON T.col1 = TB.col1 INSERT INTO testB(col1,col2,col3)
SELECT * FROM INSERTED
END
UPDATE dbo.TB
SET col2 = 55
WHERE col1 =2
SELECT * FROM dbo.TB/*
col1 col2 col3
1 2 NULL
2 55 NULL
3 5 NULL*/SELECT * FROM testB/*
col1 col2 col3
2 55 NULL*/