Writing triggers for tables with text, ntext or image

Question

Writing triggers for tables with text, ntext or image

adamstanton1979

SSC Veteran

Points: 221
More actions
November 15, 2007 at 5:21 pm

#70281

Hi,
I have a question for anyone who may have encountered the difficulty I'm facing right now. I have written a sleuth of triggers on our Microsoft SQL 2000 database to record changes in data for certain tables (data auditing requirements). I am facing a problem with the way ntext fields are referenced from the inserted and deleted tables. I am receiving the following error:
"Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables."
I did some research and found that Microsoft does not allow references to columns of variable type ntext, text or image for Microsoft SQL 2000: (http://msdn2.microsoft.com/en-us/library/aa214435(SQL.80).aspx)
"SQL Server 2000 does not allow text, ntext, or image column references in the inserted and deleted tables for AFTER triggers; however, these column references are allowed for INSTEAD OF triggers. For more information, see CREATE TRIGGER."
So I have the following questions:
1) Does SQL Server 2000 allow me to access ntext fields from the inserted or deleted tables? If so, why I am getting the above error?
2) The error message suggests to me that the ntext fields are present in the table but I just can not use them. Am I right in thinking that? If so, is there any way I can access them?
3) Does anyone know of any work around I can use to access these fields?
I have a feeling I'm out of luck but thought I would try!
Thanks in advance for you help.
Adam.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

IceCreamWizard SSC Enthusiast Points: 171 More actions · Answer 1

Disclaimer: I'm new to SQL Server this year and I have not tried this solution. However, it may provide you with some ideas or prompt a more experience SQL Server developer with a better answer.

I'm assuming the following...

CREATE TABLE TableA (

id int IDENTITY(1, 1) NOT NULL,

docTitle nvarchar(200) NOT NULL,

docText ntext NULL,

docSize bigint NULL,

PRIMARY KEY ([id])

)

Try the following...

CREATE TRIGGER myProjTR_AU_TableA on TableA

AFTER TRIGGER

AS

insert into

TableA_Audit (Title, DocText, DocSize, DateUpdated)

select

i.docTitle, t.docText, i.DocSize, getDate()

from

inserted as i,

TableA as t

where

i.id = t.id

In short, since you're writing [font="Courier New"]AFTER UPDATE[/font] trigger, simply grab the info you need from the table itself vice the [font="Courier New"]inserted[/font] row.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

So I have the following questions:
1) Does SQL Server 2000 allow me to access ntext fields from the inserted or deleted tables? If so, why I am getting the above error?
2) The error message suggests to me that the ntext fields are present in the table but I just can not use them. Am I right in thinking that? If so, is there any way I can access them?
3) Does anyone know of any work around I can use to access these fields?

1) No... just like BOL says, you cannot access Text, NText, or Image columns from the Inserted or Deleted tables.

2) No, the "fields" are not present in the trigger. Only place holders for them are.

3) Yes, and the IceCreamWizard's suggestion (above) is spot-on. Use the PK column(s) in the Inserted/Deleted tables to identify which rows you want to work in the "real" table that has the Text, NText, or Image columns.

Please, don't tell me that you're trying to "audit" such columns...

Use the IceCreamWizard's suggestion above. Use

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)