SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Any alternate to Instead of Trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rocknpop
Posting Yak Master

184 Posts

Posted - 05/13/2013 :  10:15:21  Show Profile  Reply with Quote
Hi, I have a instead of update trigger which checks for columns being updated. I need to know any alternate to this, here is what the trigger is doing:

SELECT @TOTALCOUNT = COUNT(*) FROM INSERTED WHERE UPDATE(COL_A) OR UPDATE(COL_B) OR UPDATE(COL_C)

The no. of columns can vary from 100-500. What I need to know is a way to check if at least one column is updated, that is all I need. Is there a way to achieve this in a procedure, maybe using hashbytes for comparing rows or by using output clause or merge in some way in a procedure.

Thanks

--------------------
Rock n Roll with SQL

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 05/13/2013 :  10:23:58  Show Profile  Reply with Quote
You could compare the binary_checksum(*) of inserted against binary_checksum(*) of deleted. http://technet.microsoft.com/en-us/library/ms173784(v=sql.100).aspx
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 05/13/2013 :  12:05:03  Show Profile  Reply with Quote
Thanks James.

I was using hasbytes over binary_checksum but hasbytes has lmit of 8000 characters. I found a sys function:fn_repl_hash_binary which generates MDF. I am trying something as below to update hashbytes column after table creation, cannot think of anything else. This is giving me an error-

"Unnamed tables cannot be used as XML identifiers as well as unnamed columns cannot be used for attribute names. Name unnamed columns/tables using AS in the SELECT statement."

DECLARE @SQ VARCHAR(MAX)
SET @SQ = STUFF(
(SELECT '+' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T3' FOR XML PATH(''))
,1,1,'')

SET @SQ = ' (SELECT CAST(' + REPLACE(@SQ,'+',' AS VARCHAR)+CAST(') + ' AS VARCHAR) FROM T3)'
--ALTER TABLE T3 ADD HASHBYTE VARBINARY(MAX)
--SELECT @SQ

SET @SQ = '
UPDATE T3 SET HASHBYTE = (
select master.sys.fn_repl_hash_binary(CONVERT(VARBINARY(MAX),(SELECT ' + @SQ + ' FROM T3 FOR XML RAW) ))
)'
EXEC (@SQ)

--------------------
Rock n Roll with SQL
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 05/13/2013 :  12:14:00  Show Profile  Reply with Quote
ok some update, hasbytes seems to work if we convert to varchar(max). so in the above instead of using the system function we can use hasbytes as below but the error is the same, here is the updated code but the erros is still the same as above, any thoughts -


DECLARE @SQ VARCHAR(MAX)
SET @SQ = STUFF(
(SELECT '+' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='T3' FOR XML PATH(''))
,1,1,'')
SET @SQ = ' (SELECT CAST(' + REPLACE(@SQ,'+',' AS VARCHAR)+CAST(') + ' AS VARCHAR) FROM T3)'
--ALTER TABLE T3 ADD HASHBYTE VARBINARY(MAX)
--SELECT @SQ

SET @SQ = '
UPDATE T3 SET HASHBYTE = (
select HASHBYTES(''SHA1'',(CONVERT(VARCHAR(MAX),(SELECT ' + @SQ + ' FROM T3 FOR XML RAW) )))
)'
EXEC (@SQ)

--------------------
Rock n Roll with SQL
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 05/13/2013 :  13:04:48  Show Profile  Reply with Quote
Can you try something like shown below - the PK_COLUMN is the primary key column of the table
;WITH a AS 
( SELECT PK_COLUMUN, BINARY_CHECKSUM(*) AS ChkSum FROM INSERTED),
b AS
( SELECT PK_COLUMUN, BINARY_CHECKSUM(*) AS ChkSum FROM DELETED)
SELECT
	@TOTALCOUNT  = COUNT(*)
FROM
	b INNER JOIN a ON a.PK_COLUMUN = b.PK_COLUMUN
WHERE 
	a.ChkSum <> b.ChkSum;
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 05/13/2013 :  13:59:40  Show Profile  Reply with Quote
Hi James, Yes this should work but I cannot use a * as not all columns will be part of the checksum.

--------------------
Rock n Roll with SQL
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 05/13/2013 :  15:14:03  Show Profile  Reply with Quote
Wouldn't querying against the INFORMATION_SCHEMA.COLUMNS that you were attempting to do also pick up all columns (unless you filtered them out/in)?

Regardless, you can explicitly list the columns that you want to participate in the checksum, for example like shown below. If you have a lot of columns, the query will look a little long, but that should not cause any adverse effects other than being unsightly to look at:

SELECT PK_COLUMN, BINARY_CHECKSUM(col1,col2,col7,col23) as ChkSum FROM INSERTED
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 05/13/2013 :  15:39:26  Show Profile  Reply with Quote
Yes I started off with INFORMATION_SCHEMA.COLUMNS but the code became quite tricky and messy as it was all dynamic. But yes that is another way to do this. The whole idea is to not to hard-code the columns names and move away from the trigger if possible. But I guess trigger would be needed and your query will be perfect provided I already have a checksum. So, I created a checksum field on the creation of the table and it looks like as below. this table has been created dynamically with columns being passed as parameters which are parsed to build the hashbytes:

CREATE TABLE Demo(
ID INT IDENTITY(1,1) ,
A VARCHAR(400),
B VARCHAR(400),
C VARCHAR(400),
CHKSUM AS HASHBYTES('SHA1',(CONVERT(VARCHAR(MAX),CAST(ISNULL(A,'NA') AS VARCHAR)+ '|' + CAST( ISNULL(B,'NA') AS VARCHAR)+ '|' + CAST( ISNULL(C,'NA' ) AS VARCHAR)))),
CONSTRAINT Demo_PK PRIMARY KEY (ID)) ON [PRIMARY];

I have question now, suppose I insert the following 3 records, they all created 3 different hashbytes(2nd row I can understand as I added a pipe character after each column, 3rd is due to case-sensitivity). How to handle case-insensitivity?

INSERT INTO Demo VALUES('A','B','C')
INSERT INTO Demo VALUES('B','A','C')
INSERT INTO Demo VALUES('a','b','C')

--------------------
Rock n Roll with SQL
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 05/14/2013 :  14:50:55  Show Profile  Reply with Quote
James, a question. Will the following too work to check for changed rows (assuming we are using a * for now:

SELECT * FROM INSERTED
EXCEPT
SELECT * FROM DELETED

IF @@ROWCOUNT > 0
PRINT 'CHANGED'


--------------------
Rock n Roll with SQL
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3707 Posts

Posted - 05/14/2013 :  15:56:44  Show Profile  Reply with Quote
Yes, it should work - assuming it is an update operation.
Go to Top of Page

rocknpop
Posting Yak Master

184 Posts

Posted - 05/14/2013 :  16:19:18  Show Profile  Reply with Quote
Yes will be an update in this case. Thanks

--------------------
Rock n Roll with SQL
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000