ntext and update/insert triggers

[Derek Erb]

SQL Server 2000 : I have a series of tables which all have the same
structure. When any of these tables are modified I need to syncrhonise
all of those modifications with one other table wich is a sort of merge
of the individual tables with one extra column.

For most of these tables this is not a problem. The problem arrives
when one of the tables has an ntext column which obviously can not be
used in an update or insert trigger.

Here's an example of one of them:

CREATE TABLE tblImages(
ID INT IDENTITY(1,1) PRIMARY KEY,
Inventory nvarchar(8) NOT NULL,
Coll nvarchar(8) NOT NULL,
ImageFile nvarchar(128) NOT NULL,
ImageNotes ntext NULL,
TS timestamp NULL
CONSTRAINT U_Images UNIQUE NONCLUSTERED (ItemCode, Inventory, Coll,
ImageFile)

I then had created an update trigger which looked like this:

CREATE TRIGGER COLLNAME_UTRIGGER ON COLLNAME_Images
FOR UPDATE
AS
BEGIN
UPDATE tblImages SET
Inventory = inserted.Inventory,
Coll = 'COLLNAME',
ImageFile = inserted.ImageFileName,
FROM inserted INNER JOIN tblImages ON inserted.ItemCode =
tblImages.ItemCode AND
inserted.Invventory = tblImages.Invventory AND tblImages.Coll =
'COLLNAME' AND
inserted.ImageFileName = tblImages.ImageFile

UPDATE tblImages
SET ImageNotes=inserted.Notes
FROM inserted INNER JOIN tblImages ON inserted.ItemCode =
tblImages.ItemCode AND
inserted.Inventory= tblImages.Inventory AND tblImages.Coll =
'COLLNAME' AND
inserted.ImageFileName = tblImages.ImageFile
END " & vbCrLf)

The first update in my trigger, be it an update or insert trigger,
works fine. It crashes with the "Cannot use text, ntext or image
columns in the 'inserted' or 'deleted' tables." error in the second
part.

I have read various messages through the Internet on this and several
of them reference using INSTEAD OF triggers and views. I have never
used those before as this is my first work with SQL 2000. None of the
examples of INSTEAD OF triggers I have seen yet use the actual inserted
tables and I haven't quite understood how to use them correctly.

Can someone help me with the basic syntax as this trigger is one of
several that I am going to have to get working.

Thank you in advance for any help, assistance, suggestions or
"direction pointing" you may provide.

[Hugo Kornelis]

On 13 May 2005 06:17:29 -0700, Derek Erb wrote:

(snip)

Hi Derek,

I don't really understand your question. First, you write that the
tablles all have the same structure, then you indicate that one of the
tables has an ntext column. If they are all the same structure, wouldn't
they all have this ntext column?

Also, you are storing loads of redundant data. I suggest that you drop
all tables except the merge table (that holds all data from all other
tables plus the extra column). Then create views to mimic the old
tables. I guess that this would solve most if not all your problems!

Finally, to answer your question: ntext and image columns are not
available in the inserted pseudotable (except when you use an INSTEAD OF
trigger, but I wouldn't recommend them in your case). The workaround is
to fetch the ntext or image data from the base table by joining the
inserted pseudotable to the base table on all the key columns.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)