我重写了下: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO --========================================================= --删除原来有的触发器 if exists(select name from sysobjects where name='tri_up_sql') drop trigger tri_up_sql go -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= CREATE TRIGGER tri_up_sql ON [dbo].[sql] for UPDATE AS --declare @tm nvarchar(50), @nr ntext, @ty nchar(5) BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;insert [dbo].[sql_bak] (cID,cTiMu,cNeiRong,cType) select cID,cTiMu, cNeiRong, cType from [dbo].[sql] where dbo.sql.cID = (select cID from DELETED)END可是修改原表后,备份表的数据不是修改前的,而是修改后的,请帮我看下错在哪?
--TRY dbo.sql.cID = (select cID from DELETED) --> dbo.sql.cID = (select cID from INSERTED)
set nocount on go create table t1(id int,data ntext) insert into t1 select 1,'aa' insert into t1 select 2,'bb' create table t2 (id int,data ntext)gocreate trigger trg on t1 instead of update as begin insert into t2 select * from t1 where id in(select id from deleted) delete from t1 where id in(select id from deleted) insert into t1 select * from inserted end goupdate t1 set data='cc' where id=2 select * from t1 select * from t2drop table t1 drop table t2set nocount off go/*测试结果 id data ----------- ------- 1 aa 2 ccid data ----------- ------- 2 bb */
用了insead of触发器就可以访问二进制字段了,也不需要去根据id去查询,类似如下 create trigger trg on t1 instead of update as begin insert into t2 select * from deleted delete from t1 where id in(select id from deleted) insert into t1 select * from inserted end
自己檢查下 對象名寫錯了 不是INSTERD 是INSERTED
见#6楼的解决方案create trigger trg on t1 instead of update as begin insert into t2 select * from deleted delete from t1 where id in(select id from deleted) insert into t1 select * from inserted end
我重写了下:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--=========================================================
--删除原来有的触发器
if exists(select name from sysobjects where name='tri_up_sql')
drop trigger tri_up_sql
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE TRIGGER tri_up_sql
ON [dbo].[sql]
for UPDATE
AS
--declare @tm nvarchar(50), @nr ntext, @ty nchar(5)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;insert [dbo].[sql_bak]
(cID,cTiMu,cNeiRong,cType)
select cID,cTiMu, cNeiRong, cType from [dbo].[sql] where
dbo.sql.cID = (select cID from DELETED)END可是修改原表后,备份表的数据不是修改前的,而是修改后的,请帮我看下错在哪?
dbo.sql.cID = (select cID from DELETED)
-->
dbo.sql.cID = (select cID from INSERTED)
运行出现如下错误:
消息 208,级别 16,状态 1,过程 tri_up_sql,第 16 行
对象名 'INSTERD' 无效。
go
create table t1(id int,data ntext)
insert into t1 select 1,'aa'
insert into t1 select 2,'bb'
create table t2 (id int,data ntext)gocreate trigger trg on t1 instead of update
as
begin
insert into t2 select * from t1 where id in(select id from deleted)
delete from t1 where id in(select id from deleted)
insert into t1 select * from inserted
end
goupdate t1 set data='cc' where id=2
select * from t1
select * from t2drop table t1
drop table t2set nocount off
go/*测试结果
id data
----------- -------
1 aa
2 ccid data
----------- -------
2 bb
*/
create trigger trg on t1 instead of update
as
begin
insert into t2 select * from deleted
delete from t1 where id in(select id from deleted)
insert into t1 select * from inserted
end
對象名寫錯了
不是INSTERD
是INSERTED
as
begin
insert into t2 select * from deleted
delete from t1 where id in(select id from deleted)
insert into t1 select * from inserted
end