# Miscellaneous > General Database Discussions >  Text data type from deleted table (trigger)

## Christine

Hi,

I need to prevent modification/update to a field.

So I created a trigger. To take the data from DELETED table then replace the field data. 

However, I have problem with one field which data type is text. 

MS SQL always return me this error:
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables.

I use the following code to take the data from deleted table

DECLARE @ptrval varbinary(16)
SELECT @ptrval = TEXTPTR(NOTES )
FROM DELETED

I also tried simple Select statement
Select notes from deleted

What code should I use to take the deleted data

Please help. 

Thanks in advance

----------


## rmiao

In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70. 

If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.

----------


## Christine

Hi Rmiao,

I have set the compatibility to 80

however, I'm still not able to use the instead of trigger as u suggested. Is there anything wrong the syntax.

The purpose of my trigger is to prevent user from updating Notes field in Contsupp database

Below is my trigger


CREATE TRIGGER NO_UPDATE_TO_NOTES
ON CONTSUPP
INSTEAD OF UPDATE
AS 

IF UPDATE (NOTES)
BEGIN
	DECLARE @@CONTACT CHAR(30)
	SELECT @@CONTACT=LTRIM(RTRIM((SELECT U_CONTACT FROM DELETED)))
	IF @@CONTACT='COMMENTS'
		BEGIN
			UPDATE CONTSUPP SET NOTES=(SELECT NOTES FROM DELETED)
			WHERE RECID=(SELECT RECID FROM DELETED)
		END
END
==================
Server: Msg 279, Level 16, State 3, Procedure NO_UPDATE_TO_NOTES, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.


Thanks in advance

rgds,

Christine

----------


## rmiao

Is notes a text column? If so, can't use it in subquery.

----------


## Christine

Yes, Notes field is text column. 

mmm, any suggestion how to prevent them from editing that particular column?

----------


## andi_g69

Try to refine your Update as follows:

UPDATE contsupp
SET notes = d.notes
FROM contsupp cs
join deleted d on d.recid = cs.recid

----------


## Christine

Still can't, andi. Hikss..

----------


## David Fine

I haven't tested this today, but in times past I have sometimes pulled data from a text field by concatenating parts of the field.

SQL-Server lets you do things with a SUBSTRING that can't be done by selecting the text data type column directly.

SELECT SUBSTRING(textfield,1,2000)
|| SUBSTRING(textfield,2000,2000)
|| SUBSTRING(textfield,4000,2000)
|| SUBSTRING(textfield,6000,2000)
from tablename

Maybe this will give an alternate solution that could work around the problem.

----------


## andi_g69

Hi Christine

I have checked this out on my development machine (Win2k, SQL2K SP3) and it works. 

* That's the table definition I am using:
CREATE TABLE [dbo].[tblTest] (
	[pk_test] [int] IDENTITY (1, 1) NOT NULL ,
	[txt_test] [text] COLLATE SQL_Latin1_General_CP437_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblTest] ADD 
	CONSTRAINT [PK_tblTest] PRIMARY KEY  CLUSTERED 
	(
		[pk_test]
	)  ON [PRIMARY] 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE TRIGGER NO_UPDATE_TO_NOTES
ON tblTest
INSTEAD OF UPDATE
AS 

IF UPDATE (txt_test)
BEGIN
UPDATE tbltest
SET txt_test = d.txt_test
FROM tbltest t
join deleted d on d.pk_test = t.pk_test
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

* My test data:
pk_test,txt_test
1,Rec 1
2,Rec 2
3,Rec 3

* My Update statement:
Update tbltest set txt_test = 'Rec 1 modified' where pk_test = 1

* The data after the update:
pk_test,txt_test
1,Rec 1
2,Rec 2
3,Rec 3

----------


## Christine

Hi Andi, thanx
It works well.

Thanx to David and rmiao too.

----------

