| Author |
Topic |
|
rocknpop
Posting Yak Master
201 Posts |
 Posted - 2013-05-13 : 10:15:21
|
| 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-13 : 10:23:58
|
| 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 |
 |
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-13 : 12:05:03
|
| 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 @SQSET @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 |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-13 : 12:14:00
|
| 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 @SQSET @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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-13 : 13:04:48
|
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_COLUMUNWHERE a.ChkSum <> b.ChkSum; |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-13 : 13:59:40
|
| 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 |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-13 : 15:14:03
|
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 |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-13 : 15:39:26
|
| 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 |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-14 : 14:50:55
|
| James, a question. Will the following too work to check for changed rows (assuming we are using a * for now:SELECT * FROM INSERTEDEXCEPTSELECT * FROM DELETEDIF @@ROWCOUNT > 0 PRINT 'CHANGED'--------------------Rock n Roll with SQL |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-14 : 15:56:44
|
| Yes, it should work - assuming it is an update operation. |
|
|
|
rocknpop
Posting Yak Master
201 Posts |
Posted - 2013-05-14 : 16:19:18
|
| Yes will be an update in this case. Thanks--------------------Rock n Roll with SQL |
|
|
|
|
Any alternate to Instead of Trigger