SQL Server 2008 R2：在“ TEXT”列上触发 [英] SQL Server 2008 R2: Trigger on `TEXT` column

问题描述

我有一个表，该表由数据类型为 text 的列组成。

I have the table which consist of column with datatype text.

表 EmployeeMaster

Table EmployeeMaster

CREATE TABLE [EmployeeMaster]
(
    EmpID int identity(1,1),
    EmpName varchar(20),
    EmpPhone int,
    EmpAddress TEXT
);

我想在此表上创建审核日志。

And I want to create audit log on this table.

审计表：EmployeeMaster_Audit

Audit Table: EmployeeMaster_Audit

CREATE TABLE [EmployeeMaster_Audit]
(
    EmpID int,
    EmpName varchar(20),
    EmpPhone int,
    EmpAddress VARCHAR(MAX)
);

INSERT的写入触发器。

Writing trigger for INSERT.

触发器：

CREATE TRIGGER [dbo].[EmployeeMaster_Insert]
ON [dbo].[EmployeeMaster]
FOR INSERT   
AS   

 INSERT INTO [dbo].[EmployeeMaster_Audit]
([EmpID], [EmpName], [EmpPhone], [EmpAddress])
SELECT CONVERT(int,[EmpID]) as [EmpID],[EmpName],[EmpPhone],CONVERT(varchar(max),[EmpAddress]) AS [EmpAddress]  FROM INSERTED 
GO

错误详细信息：创建触发器时出现以下错误：

Error Details: While creating trigger getting following error:

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

Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

我的尝试： CONVERT（varchar（max），[EmpAddress]）

推荐答案

由于在插入后 触发了触发器，因此您只需查询回到 EmployeeMaster 进入锯齿数据。像这样：

Since the trigger is fired after the insert, you can simply query back to the EmployeeMaster to get the inserted data. Something like this:

CREATE TRIGGER [dbo].[EmployeeMaster_Insert]
ON [dbo].[EmployeeMaster]
FOR INSERT   
AS   

INSERT INTO [dbo].[EmployeeMaster_Audit] ([EmpID], [EmpName], [EmpPhone], [EmpAddress])
SELECT  EM.[EmpID]
,       EM.[EmpName]
,       EM.[EmpPhone]
,       CONVERT(varchar(max), EM.[EmpAddress]) AS [EmpAddress]  
FROM    INSERTED I
INNER JOIN  dbo.[EmployeeMaster] EM
        ON  EM.[EmpID] = I.[EmpID]
GO

这是假设您无法更改文本数据类型，请参见Zohar的回答。

This is assuming you cannot change the text datatype, see Zohar's answer.

这篇关于SQL Server 2008 R2：在“ TEXT”列上触发的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！