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.

Advertisement