SQL Server Performance Forum – Threads Archive

Update text tield in triggers

Can we update Text field through triggers?
Surendra Kalekar

I don’t see a problem (besides performance). Have you tested it? If so, what was the problem?

quote:Originally posted by mmarovic I don’t see a problem (besides performance). Have you tested it? If so, what was the problem?

Yes, I tested it but it was not updating the text field. Don’t know why?
Surendra Kalekar

Can you post a code?

Create TRIGGER Trg_RfpTitle_NonASCII ON RFP
FOR INSERT,UPDATE
AS
BEGIN
if @@ROWCOUNT = 1
Begin
update RFP
set [DESCRIPTION] = dbo.Remove_ASCII_Char (inserted.[DESCRIPTION]),
[TitlePublic] = dbo.Remove_ASCII_Char (inserted.[TitlePublic])
from inserted
where RFP.RFPID = inserted.RFPID
End
END Both the field are text field. click on the below link to find out dbo.Remove_ASCII_Char funtion
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10555 Surendra Kalekar

Can you post a query that fires the trigger?

No, you cannot update text field in a trigger. IN SQL 6.5 text columns were available in triggers with null values ( meaning inserted/deleted tables will have null values for text fields ) but from SQL 7.0 onwards you cannot access them in triggers. YOu have to use procedures for that.

Good catch, there is assignment of inserted.[description] in the trigger. I thought problem is the use of @@rowCount. BOL:

quote: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. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.
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.

quote:Originally posted by mmarovic Good catch, there is assignment of inserted.[description] in the trigger. I thought problem is the use of @@rowCount. BOL:

Thanks mmarovic and indshri.
Executing sp in trigger will work and is there any disadvantages? Just confirming because, I never called sp within triggers.
Surendra Kalekar

True it can lead to a deadlock situation and that too when using text data types. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Sorry Satya I deleted my post thinking it doesn’t add anything to discussion. So I’m putting it back, because you followed my answer with additional info. I said I don’t have much experience with triggers because I always try to avoid them due to perfromance reasons.

Mirko
I appreciate all your input in the threads, in fact I do learn few things which are unknown to me.
Keep it up. Previously I have had lookup for an article explaining the disadvantages of using SP from a trigger, I’m unable to findout right now. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks for the kind words. Unfortunatelly I couldn’t find the article either.

Thanks Satya and mmarovic.
How I can update text field in this situation? Is there any different way to do it?
Surendra Kalekar

Have you referred to UPDATETEXT statement in BOL. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>