Trigger with a "text" Data Type 1

[davidy]

Hi,

I’m making some triggers to move data between two databases.
But I run into the following problem, one of the fields has as data type “text”.
When I try to use this field in the trigger the following message is returned:
“Error 2739: The text, ntext, and image data types are invalid for local variables.”

I have tried several options to try and resolve this. In the code example below I have marked them (1 and 2).

When I try option 1 I get the error message, with option 2 I don’t get an error message but it inserts “NULL” in the message field.


CREATE TRIGGER [trgReturnTemplateMessages] ON [dbo].[cwReturnTemplateMessages]
1) FOR INSERT
2) AFTER INSERT
AS

DECLARE @comID AS bigint
1) DECLARE @comMessage AS text
2) DECLARE @comMessage AS varchar(8000)


SELECT @comID = (SELECT ID FROM inserted)
1) SELECT @comMessage = (SELECT Message FROM inserted)
2) SELECT @comMessage = (SELECT CAST(cwReturnTemplateMessages.Message as varchar(8000)) FROM inserted, cwReturnTemplateMessages WHERE inserted.ID = cwReturnTemplateMessages.ID)

INSERT dbo.comReturnTemplateMessages VALUES (@comID, @comMessage)


Do you maybe have a solution for my problem?

 

[nigelrivett]

Odd I thought 2 should give an error too.
Don't try to access the data via variables - join to the table.
If you need to do row by row processing then use the id to join to the inserted table.

INSERT dbo.comReturnTemplateMessages
SELECT inserted.ID, cwReturnTemplateMessages.Message
FROM inserted
join cwReturnTemplateMessages
on inserted.ID = cwReturnTemplateMessages.ID


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[SQLSister]

Going along with what nigel said, you never want to use a trigger that inserts with variables. If you have a multi-record insert, it won't work properly. Always design triggers for multiple record inserts or updates or deletes.



Questions about posting. See faq183-874