Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Comparing ntext in a trigger

Author  Topic 

WaterWolf
Starting Member

24 Posts

Posted - 2010-08-06 : 06:41:36
I have a legacy ntext field in a table. I want to add a simple trigger that will check if the ntext has changed after an update. Eg.


CREATE TRIGGER [FieldUpdated]
ON [MyTable]
AFTER UPDATE
AS
DECLARE @oldValue nvarchar(max)
DECLARE @newValue nvarchar(max)
IF (UPDATE (TextField))
BEGIN
SET @oldValue = (SELECT CONVERT(nvarchar(max), TextField) FROM Deleted)
SET @newValue = (SELECT CONVERT(nvarchar(max), TextField) FROM Inserted)

IF (@oldValue != @newValue)
BEGIN
-- Do something.
END
END
GO


This will cause an error as sql server will not let me select an ntext value from Inserted or Deleted. Is there a way around this? I heard it mentioned that using an INSTEAD OF trigger would work but I'm not sure how as surely I'd still be trying to select the ntext value from Inserted or Deleted?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-06 : 07:41:06
This is from Books On Line
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 set 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.

For example,


SELECT i.textdate
from yourtable y
inner join
inserted i
on
y.pk = i.pk

Jim






Everyday I learn something that somebody else already knew
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 08:39:45
In the old days of SQL 2000 we used INSTEAD OF TRIGGERs (which are a PITA compared to AFTER triggers) - I reckon that will work, we ONLY ever used them where we had N/TEXT that we wanted to manipulate in the Trigger. But its a much easier solution to change the column datatype to N/VARCHAR(MAX)

Where we couldn't be bothered to sue INSTEAD OF TRIGGERs we compared the first 8,000 of N/TEXT columns and their LENGTH to see if they were different (but that was obviosuly risky that something was the same length, and only different past the first 8,000 characters - might do for your application though.

Dunno if you can use CHECKSUM on the columns in an AFTER Trigger? Obviously not a 100% thing that they are different, but might be "good enough"?
Go to Top of Page

WaterWolf
Starting Member

24 Posts

Posted - 2010-08-06 : 10:47:58
jimf: Thanks for the suggestion. Joining the insert table to the original table would allow me to get the current updated text for the field however I still wouldn't have the previously deleted value to compare it to.

Kristen: I reworked the trigger to use an INSTEAD OF (I don't really understand why you're allowed use ntext in one type of trigger but not the other) and it appears to be working now. It's a bit of a pain to have to list all columns in the table in the trigger but it's quite usable. The trigger now looks like:


CREATE TRIGGER [FieldUpdated]
ON [MyTable]
INSTEAD OF UPDATE
AS
UPDATE MyTable SET OtherField1 = (SELECT OtherField1 FROM Inserted),
OtherField2 = (SELECT OtherField2 FROM Inserted),
OtherField3 = (SELECT OtherField3 FROM Inserted),
TextField = (SELECT TextField FROM Inserted)
WHERE tableId = (SELECT tableId FROM Inserted)

IF (UPDATE (TextField))
BEGIN
DECLARE @oldValue nvarchar(max)
DECLARE @newValue nvarchar(max)

SET @oldValue = (SELECT CONVERT(nvarchar(max), TextField) FROM Deleted)
SET @newValue = (SELECT CONVERT(nvarchar(max), TextField) FROM Inserted)

IF (@oldValue != @newValue)
BEGIN
-- Do something.
END
END
GO
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-06 : 11:00:54
If you're using CONVERT(nvarchar(max)) in the trigger, why not just alter the column to nvarchar(max)?
Go to Top of Page

WaterWolf
Starting Member

24 Posts

Posted - 2010-08-06 : 11:06:15
quote:
Originally posted by robvolk

If you're using CONVERT(nvarchar(max)) in the trigger, why not just alter the column to nvarchar(max)?



It's a legacy field being written to by an old C++ programme. It's easier just to fix this trigger than trying to find all the side effects of changing the column type.
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2010-08-06 : 11:41:10
"It's a bit of a pain to have to list all columns in the table in the trigger but it's quite usable."

... until you add a column to the table and the Instead Of Trigger doesn't include it in the UPDATE - but, maybe, for a "Legacy Table" that won't happen

Some other side effects we had, forgotten what now. I have a vague memory it was to do with ARITH ABORT where we had a calculated column in the table, or the INSTEAD OF Trigger was on a VIEW (they were too much of a PITA so although we got excited by them we couldn't justify the Maint. work in using them at the end of the day)
Go to Top of Page
   

- Advertisement -