SQL Trigger Cannot Do Instead of Delete But Is Required for Ntext, Image Columns

SQL Trigger cannot do INSTEAD OF DELETE but is required for ntext, image columns

I found out that, even if you delete multiple rows at once, each row is still put into the DELETED temporary table during the query execution, so my trigger then goes through each record and matches on recid(which is unique to each row) and deletes/moves from there.

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

A nice solution has been found:

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

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

There is not.

text, ntext and image are not accessible in this form.

If you must, select the primary key from inserted and look up the value in the actual underlying table.

How to use text, ntext, or image columns in the 'inserted' and 'deleted' tables

As of SQL Server 2005, TEXT/NTEXT/IMAGE are deprecated - you should use the (N)VARCHAR(MAX) and VARBINARY(MAX) data types instead.

(N)VARCHAR(MAX) (see MSDN docs here) and VARBINARY(MAX) allow up to 2 GByte of data

From the MSDN docs:

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through
4,000. max indicates that the maximum storage size is 2^31-1 bytes. (= 2 GB)

The (N)VARCHAR(MAX) types also allow all the usual T-SQL string function to work on them - something that wasn't the case with (N)TEXT at all.

As this MSDN article shows, the replacement types are supported in triggers, too:

SQL Server 2008 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.

How to get use text columns in a trigger

the only way to do this is keep the current row in the history table too. Do what you tried and join to the actual table, and insert all columns from current actual table into the history table.

you probably have something like this, where the history only has the previous version of a row:

YourTable
ID  value1   value2
1   AAA      AAAA
2   BBB      BBBB
3   CCC      CCC3

YourTableHostory
HistoryID HistoryDate ID value1  value2
1         4/17/2010   2  CCC     CCCC
2         4/18/2010   2  CCC     CCC1

I'm saying do something like this, where every version is stored:

YourTable
ID  value1   value2
1   AAA      AAAA
2   BBB      BBBB
3   CCC      CCC3

YourTableHostory
HistoryID HistoryDate ID  value1  value2
1         4/10/2010   1   AAA      AAAA
2         4/10/2010   2   BBB      BBBB
3         4/10/2010   3   CCC      CCCC
4         4/17/2010   2   CCC      CCC1
5         4/18/2010   2   CCC      CCC2
5         4/19/2010   2   CCC      CCC3

this way whenever there is an insert or update, just insert the current row into the history table, you have a duplicate of the current row, but that isn't that terrible.

If you are just adding this trigger to an existing table with data in it, then run a query like this to prepopulate all the current values for you:

INSERT INTO YourTableHostory
        (HistoryDate,ID,value1,value2)
    SELECT
        GETDATE(),ID,value1,value2
        FROM YourTable

SQL Server 2008 R2: Trigger on `TEXT` column

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

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

Delete records within Instead Of Delete trigger

This method should do the trick. An instead of trigger will do whatever you specify instead of doing the delete automatically. This means it is key that you do the delete in it manually or the record will not get deleted. It will not run recursively. It can only be done on a table without cascade delete enabled. Basically the trick is you join to the orginal table on the id field in order to get the data from the field you don't have access to in the deleted pseudotable.

create table dbo.mytesting (test_id int, sometext text)
go
create table dbo.myaudit (test_id int, sometext text)
go
insert into dbo.mytesting
values (1, 'test')
go

Create Trigger audit_Table_Deletes on dbo.mytesting INSTEAD OF delete  
as 
if @@rowcount = 0 return; 
Insert into dbo.myaudit (test_id, sometext) 
Select d.test_id, t.sometext from deleted d 
join dbo.mytesting t on t.test_id = d.test_id

Delete dbo.mytesting where test_id in (select test_id from deleted)
go

delete dbo.mytesting where test_id = 1
select * from dbo.mytesting
select * from dbo.myaudit 
Go 

drop table dbo.mytesting
drop table dbo.myaudit

If you can change the field to varchar(max) or nvarchar(max) that is the best solution though. Text and ntext are deprecated and should be removed altogether from SQL Server in the next version.

---

Related Topics