How can I detect changes in a row of some table

It is possible to use a trigger check for changes made to a column, but a few obstacles are present as the column you are looking to verify is of a data type xml.  The below code shows the difficulty in comparing the xml data type to another xml data type:
\
DECLARE @data1 XML,
@data2 XML
SET @data1 = '<root />'
SET @data2 = '<empty />'
IF @data1 <> @data2
BEGIN
PRINT 'Different'
END
ELSE
PRINT 'Same'
--Msg 305, Level 16, State 1, Line 8
The xml data type cannot be compared or sorted, except when using the IS NULL operator.
That being said it is possible to convert the xml data type to a more flexible type, but there is overhead involved in this also, the conversion.  The below code gives an example of using a trigger to compare an xml column:
USE tempdb;
GO
CREATE TABLE changetbl(
id UNIQUEIDENTIFIER,
data XML
);
GO
CREATE TRIGGER change
ON changetbl
FOR UPDATE, DELETE
AS
IF (SELECT CONVERT(VARCHAR(MAX), data) FROM INSERTED) <> (SELECT CONVERT(VARCHAR(MAX), data) FROM DELETED)
BEGIN
PRINT 'Different'
END
ELSE
PRINT 'Same'
DROP TRIGGER change;
GO
DROP TABLE changetbl;
GO
USE tempdb;
GO
CREATE TABLE changetbl(
id UNIQUEIDENTIFIER,
data XML
);
GO
CREATE TRIGGER change
ON changetbl
FOR UPDATE, DELETE
AS
IF (SELECT CONVERT(VARCHAR(MAX), data) FROM INSERTED) <> (SELECT CONVERT(VARCHAR(MAX), data) FROM DELETED)
BEGIN
PRINT 'Different'
END
ELSE
PRINT 'Same'
DROP TRIGGER change;
GO
DROP TABLE changetbl;
GO
Triggers are an awesome and powerful tool in sql, but it comes with overhead and risk.  The trigger fires automatically specific to the traansaction(s) that you specify, INSERT, UPDATE, DELETE.  In an OLTP database this can cause substantial overhead on a table that has frequent and numerous transactions.  There is also the danger of if the trigger fails so will the transaction that fired the trigger resulting in the transaction being rolled back and losing that information.
It is possible to use a trigger check for changes made to a column, but a few obstacles are present as the column you are looking to verify is of a data type xml.  The below code shows the difficulty in comparing the xml data type to another xml data type: DECLARE @data1 XML,    @data2 XML    SET @data1 = '<root />'SET @data2 = '<empty />' IF @data1 <> @data2BEGIN PRINT 'Different'ENDELSEPRINT 'Same' --Msg 305, Level 16, State 1, Line 8The xml data type cannot be compared or sorted, except when using the IS NULL operator.That being said it is possible to convert the xml data type to a more flexible type, but there is overhead involved in this also, the conversion.  The below code gives an example of using a trigger to compare an xml column: USE tempdb;GO CREATE TABLE changetbl(id UNIQUEIDENTIFIER,data XML);GO CREATE TRIGGER changeON changetblFOR UPDATE, DELETEAS IF (SELECT CONVERT(VARCHAR(MAX), data) FROM INSERTED) <> (SELECT CONVERT(VARCHAR(MAX), data) FROM DELETED)BEGINPRINT 'Different'END ELSEPRINT 'Same' DROP TRIGGER change;GO DROP TABLE changetbl;GO USE tempdb;GO CREATE TABLE changetbl(id UNIQUEIDENTIFIER,data XML);GO CREATE TRIGGER changeON changetblFOR UPDATE, DELETEAS IF (SELECT CONVERT(VARCHAR(MAX), data) FROM INSERTED) <> (SELECT CONVERT(VARCHAR(MAX), data) FROM DELETED)BEGINPRINT 'Different'END ELSEPRINT 'Same' DROP TRIGGER change;GO DROP TABLE changetbl;GOTriggers are an awesome and powerful tool in sql, but it comes with overhead and risk.  The trigger fires automatically specific to the traansaction(s) that you specify, INSERT, UPDATE, DELETE.  In an OLTP database this can cause substantial overhead on a table that has frequent and numerous transactions.  There is also the danger of if the trigger fails so will the transaction that fired the trigger resulting in the transaction being rolled back and losing that information.DECLARE @data1 XML, @data2 XML SET @data1 = '<root />' SET @data2 = '<empty />' IF @data1 <> @data2 BEGIN PRINT 'Different' END ELSE PRINT 'Same' --Msg 305, Level 16, State 1, Line 8 The xml data type cannot be compared or sorted, except when using the IS NULL operator.
Advertisements

About Saad Khan

Saad is an ASP.NET developer with 4 years of experience, and has also made a number of contributions to the Sitefinity marketplace and community. He loves to explore new code, and create innovative tools and procedures while exploring new approaches in his work with Sitefinity and MVC. As an experienced .NET developer, he frequents both Telerik and Microsoft technologies. In his spare time, Saad is an avid gamer and movie fanatic, and also experiments with new and interesting tools on the Microsoft platform.​​

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: