ntext and update/insert triggers

13 May 2005 2:24 PM

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.

13 May 2005 2:52 PM

Jacco Schalkwijk

Why not use a view instead of a trigger?

CREATE VIEW Images
AS
SELECT Inventory , 'COLLNAME' AS Coll, ImageFile , ImageNotes , TS
FROM COLLNAME
UNION ALL
SELECT Inventory , 'COLLNAME' AS Coll, ImageFile , ImageNotes , TS
FROM other table

Having a series of tables that have all the same structure is bad design to
start with. So you could also change things around and have one table to
hold all your data, and change all the other tables that you are now trying
to copy via triggers into tblImages as views, if you can't get rid of them
all together:

CREATE VIEW COLLNAME_Images
AS
SELECT Inventory , ImageFile , ImageNotes , TS
FROM tblImages
WHERE Coll = 'COLLNAME'

--
Jacco Schalkwijk
SQL Server MVP

Show quote

"Derek Erb" <derek.***@gmail.com> wrote in message
news:1115994284.034139.166540@f14g2000cwb.googlegroups.com...
> 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.
>

13 May 2005 3:12 PM

Derek Erb

Funny enough that is exactly what I am doing. I am entirely
redesigning the database. There are currently several tables and I am
merging them in to one.

But while I am doing this the users have to be able to continue to
modify their tables. That's why I'm writing the triggers which keep
the merged table(s) up to date while we're developing the new
interface. In the end we will drop the previous individual table.s

I have already written about 30 or 40 triggers for the tables which do
not have ntext columns and they all work wonderfully.

However I still can not understand how to write the trigger for a table
with an ntext column. As you can see in my examle the ImageNotes
column is an ntext column both in the individual tables and the merged
table.

I don't know what to do next...

13 May 2005 3:35 PM

Jacco Schalkwijk

If your view is a straight select from another table it is as updateable as
the table itself.

--
Jacco Schalkwijk
SQL Server MVP

Show quote

"Derek Erb" <derek.***@gmail.com> wrote in message
news:1115997138.772747.284190@o13g2000cwo.googlegroups.com...
> Funny enough that is exactly what I am doing. I am entirely
> redesigning the database. There are currently several tables and I am
> merging them in to one.
>
> But while I am doing this the users have to be able to continue to
> modify their tables. That's why I'm writing the triggers which keep
> the merged table(s) up to date while we're developing the new
> interface. In the end we will drop the previous individual table.s
>
> I have already written about 30 or 40 triggers for the tables which do
> not have ntext columns and they all work wonderfully.
>
> However I still can not understand how to write the trigger for a table
> with an ntext column. As you can see in my examle the ImageNotes
> column is an ntext column both in the individual tables and the merged
> table.
>
> I don't know what to do next...
>

13 May 2005 4:05 PM

Derek Erb

In the end I need a table (tblImages) as we will drop all of the other
tables once the new version of the interface (ASP) works and we can get
rid of the old front-end (Access).

I have already done all of the other triggers and they work great and
the new "merged" tables are great as well.

I just need 3 triggers for 3 merged tables (15 individuals as there are
5 tables per 1 merged table) that have an ntext column in them.

Isn't there some way for me to do this with triggers whether they be
AFTER triggers, which I understand and which can't do this because of
the ntext columns, or with INSTEAD OF triggers which I do not
understand but which apparently can work with ntext columns?

14 May 2005 10:14 AM

Jacco Schalkwijk

As I said, you don't need triggers to do this. Using views will give you
exactly the same functionality but with a lot less overhead.

If you really, really want to use triggers, you can join the base table to
the inserted table and get the text column from there:

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

ON i.ItemCode = c.ItemCode
AND i.Invventory = c.Invventory
AND i.ImageFileName = c.ImageFileName

--
Jacco Schalkwijk
SQL Server MVP

Show quote

"Derek Erb" <derek.***@gmail.com> wrote in message
news:1116000331.715020.228980@g49g2000cwa.googlegroups.com...
> In the end I need a table (tblImages) as we will drop all of the other
> tables once the new version of the interface (ASP) works and we can get
> rid of the old front-end (Access).
>
> I have already done all of the other triggers and they work great and
> the new "merged" tables are great as well.
>
> I just need 3 triggers for 3 merged tables (15 individuals as there are
> 5 tables per 1 merged table) that have an ntext column in them.
>
> Isn't there some way for me to do this with triggers whether they be
> AFTER triggers, which I understand and which can't do this because of
> the ntext columns, or with INSTEAD OF triggers which I do not
> understand but which apparently can work with ntext columns?
>

16 May 2005 3:51 PM

Derek Erb

Jacco,

Thank you a thousand times over!

Your base table join worked and does exactly what I need to do right
now.

You have saved my project and allowed me to move forward and beyond
this stepping stone.

Thanks!

13 May 2005 3:13 PM

Derek Erb

Please excuse the quick reply. I forgot to thank you for taking the
time to give me such a detailed and constructive suggestion. It is
well-appreciated.

13 May 2005 11:51 PM

Hugo Kornelis

On 13 May 2005 07:24:44 -0700, Derek Erb wrote:

(snip)

Hi Derek,

I just replied to your similar message in comp.databases.ms-sqlserver. I
now see that the exact same suggestion I made was already made by Jacco.
In the future, please post to one newsgroup only, or (if the question
really must be in two groups) post it as a crosspost, so that replies go
automatically to both groups as well. That saves others the time to
answer a question that is already answered elsewhere.

Best, Hugo

--

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