不能在触发器的“已插入”和“已删除”表中使用text，ntext或image列 [英] Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables in triggers

问题描述

Hello All

如果我有text作为数据类型，我的触发器不起作用。

Hello All
I have trigger that does not work if i have "text" as datatype.

create TRIGGER [dbo].[UpdateAssetDeleteLog]
   ON  [dbo].[asset_device] 
   AFTER DELETE
AS 
BEGIN
		DECLARE @device_name varchar(500)
		DECLARE @device_ip_address varchar(100)
		DECLARE @device_type varchar(100)
		DECLARE @device_desc varchar(8000)
		DECLARE @first_discovered datetime
		DECLARE @last_discovered datetime
		DECLARE @asset_profile_id int
		DECLARE @deleted_by_user int

		DECLARE  CUR_DELETED 
		CURSOR FOR 
		SELECT asset_device_name,
		asset_device_ip_address, 
		asset_device_type [Device Type],
		asset_device_desc,
		asset_device_first_discovery,		
		asset_device_last_discovery AS [Last Discovered],
		asset_profile_id
		
		
		FROM deleted
		
        OPEN    CUR_DELETED
		FETCH FROM CUR_DELETED INTO @device_name,@device_ip_address,@device_type,@device_desc,@first_discovered,@last_discovered,@asset_profile_id
		
		WHILE (@@FETCH_STATUS = 0)
        BEGIN
		INSERT INTO dbo.asset_deleted_device_detail
		(
			[device_name],
			[device_ip_address],
			[device_type],
			[device_desc],
			[first_discovered],
			[last_discovered],
			[asset_profile_id],
			[deleted_by_user],
			[deleted_date]
		)
		VALUES
		(	
			
			@device_name,
			@device_ip_address,
			@device_type,
			@device_desc,
			@first_discovered,
			@last_discovered,
			@asset_profile_id,
			1,
			getdate()
		 )

		FETCH FROM CUR_DELETED INTO @device_name,@device_ip_address,@device_type,@device_desc,@first_discovered,@last_discovered,@asset_profile_id
			
        END
        CLOSE CUR_DELETED
		DEALLOCATE CUR_DELETED

END

错误信息是这样的。

不能在'inserted'和'deleted'表中使用text，ntext或image列



任何人都可以帮助我吗？



问候

Jesu

The error message is like this.
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables

Can anyone help me?

Regards
Jesu

推荐答案

如果你只是谷歌的错误信息并且阅读一点点，很容易找到：



SQL Server不允许在AFTER触发器的inserted和deleted表中引用text，ntext或image列。但是，包含这些数据类型仅用于向后兼容目的。大数据的首选存储是使用varchar（max），nvarchar（max）和varbinary（max）数据类型。 AFTER和INSTEAD OF触发器都支持插入和删除表中的varchar（max），nvarchar（max）和varbinary（max）数据。有关更多信息，请参阅CREATE TRIGGER（Transact-SQL）。

Easy to find if you just Google the error message and read a little bit:

SQL Server does not allow for text, ntext, or image column references in the inserted and deleted tables for AFTER triggers. However, these data types are included for backward compatibility purposes only. The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types. Both AFTER and INSTEAD OF triggers support varchar(max), nvarchar(max), and varbinary(max) data in the inserted and deleted tables. For more information, see CREATE TRIGGER (Transact-SQL).

hi



一个很好的解决方案发现：

hi

A nice solution has been found:

SELECT FROM INSERTED just id column (it's not ntext or image and query is being executed).
SELECT from original table * with the same ids.
If required, use UPDATED() on INSERTED to be aware, what columns have been changed.

这篇关于不能在触发器的“已插入”和“已删除”表中使用text，ntext或image列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！