Text data type from deleted table (trigger)

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

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.

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

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

Yes, Notes field is text column.

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

Try to refine your Update as follows:

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

Still can't, andi. Hikss..

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.

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

Hi Andi, thanx
It works well.

Thanx to David and rmiao too.