触发器出现不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列

[hengbing]

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]
(cTiMu,cNeiRong,cType)
select cTiMu, cNeiRong, cType from DELETED

END
GO

运行出错提示：
消息 311，级别 16，状态 1，过程 tri_up_sql，第 16 行
不能在 'inserted' 表和 'deleted' 表中使用 text、ntext 或 image 列。

[hengbing]

我把后面这个操作直接写在存储过程中了，达到我的要求了，只是不知运行效率会不会有影响。

[ks_reny]

那就用原始表中的數據,關聯的ID用 inserted和deleted 的.

[hengbing]

[Quote=引用 1 楼 ks_reny 的回复:]
那就用原始表中的數據,關聯的ID用 inserted和deleted 的.
[/Quote]

我重写了下：
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)

[hengbing]

[Quote=引用 3 楼 happy_stone 的回复:]
SQL code--TRYdbo.sql.cID= (select cIDfrom DELETED)-->dbo.sql.cID= (select cIDfrom INSERTED)
[/Quote]

运行出现如下错误：
消息 208，级别 16，状态 1，过程 tri_up_sql，第 16 行
对象名 'INSTERD' 无效。

[jinjazz]

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)

go

create 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
go

update t1 set data='cc' where id=2
select * from t1
select * from t2

drop table t1
drop table t2

set nocount off
go

/*测试结果
id          data
----------- -------
1           aa
2           cc

id          data
----------- -------
2           bb
*/

[jinjazz]

用了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

[快乐_石头]

[Quote=引用 4 楼 hengbing 的回复:]
引用 3 楼 happy_stone 的回复:
SQL code--TRYdbo.sql.cID= (select cIDfrom DELETED)-->dbo.sql.cID= (select cIDfrom INSERTED)


运行出现如下错误：
消息 208，级别 16，状态 1，过程 tri_up_sql，第 16 行
对象名  'INSTERD' 无效。

[/Quote]
自己檢查下
對象名寫錯了
不是INSTERD
是INSERTED

[hengbing]

[Quote=引用 7 楼 happy_stone 的回复:]
引用 4 楼 hengbing 的回复:
引用 3 楼 happy_stone 的回复:
SQL code--TRYdbo.sql.cID= (select cIDfrom DELETED)-->dbo.sql.cID= (select cIDfrom INSERTED)


运行出现如下错误：
消息 208，级别 16，状态 1，过程 tri_up_sql，第 16 行
对象名  'INSTERD' 无效。


自己檢查下
對象名寫錯了
不是INSTERD
是INSERTED
[/Quote]

不报错了，但两个表记录的都是修改后的数据

[zxkid]

见#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