June 11, 2008 at 10:02 pm
I have a situation in which I get the XML of inserted records and deleted records from the AFTER UPDATE Trigger. I need to save the changes made in the records by comparing deleted ( records prior to change) with inserted (records after change). I need to get the column name with its prior value and current value.
Also I cannot check each field in the trigger as I think it will take lot of time. I am using Service Broker to send the XML of inserted and deleted in a single message.
I am totally stuck. I have found the script to compare two tables but it gives the whole records (from and to) but not the specific column(s) that are changed.
Any help will be appreciated.
Thanks
Atif Sheikh
June 12, 2008 at 8:11 pm
Try calling the following function from within the trigger. You would pass the name of the table to @TableName and the value of COLUMNS_UPDATED ( ) to @BinaryColNums. Returns a list column numbers and names that were updated. Combine that with a bit of dynamic SQL and you should be good to go.
CREATE FUNCTION dbo.fColumnsUpdated
(
@TableName SYSNAME,
@BinaryColNums VARBINARY(128)
)
RETURNS @TableInfo TABLE (RowNum SMALLINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ColNum SMALLINT NULL,
ColName SYSNAME NULL,
ColType VARCHAR(128) NULL)
AS
BEGIN --Begin the main body of the function
--===== Declare local variables
DECLARE @ByteCount TINYINT
SET @ByteCount = LEN(@BinaryColNums)
--===== Populate the return table with column numbers and names for the desired
--===== table according to the bit mask passed in through @BinaryColNums
INSERT INTO @TableInfo (ColNum,ColName,ColType)
SELECT c.ColNum, '['+sc.Name+']' AS ColName, UPPER(st.Name) AS ColType
FROM (--Derived table "c" finds column numbers and names for columns identified
--in the @BinaryColNums variable usually provided by COLUMNS_UPDATED() in
--an INSERT or UPDATE table.
SELECT CASE ---- Determine which bit # is set and use a column number
WHEN BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1)
THEN ((ByteNum)*8)+t2.n --This creates the bit# that is set
ELSE 0
END AS ColNum
FROM (--Derived table splits the bytes of the binary number from left to
--right as outline in Books OnLine for use of COLUMNS_UPDATED().
SELECT n-1 AS ByteNum,
SUBSTRING(@BinaryColNums,N,1) AS BinaryVal
FROM dbo.TALLY t1 WITH (NOLOCK)
WHERE N<= @ByteCount
) b,
Tally t2 WITH (NOLOCK)
WHERE N<=8 --8 bits in each byte
AND BinaryVal & POWER(2,t2.n-1) = POWER(2,t2.n-1) --Suppress "0" bits
) c,
dbo.SysColumns sc WITH (NOLOCK),
dbo.SysTypes st WITH (NOLOCK)
WHERE sc.ID = OBJECT_ID(@TableName)
AND sc.XType = st.XType
AND sc.ColID = c.ColNum
--===== If @BinaryColNums indicated more columns than the are, delete the ColNums from the table
DELETE @TableInfo
WHERE ColName IS NULL
--===== Return the table and exit
RETURN
END --End of Function
Oh, yeah... you'll need a Tally table... see the following URL...
http://www.sqlservercentral.com/articles/TSQL/62867/
--Jeff Moden
Change is inevitable... Change for the better is not.
June 14, 2008 at 1:37 am
Thanks for the reply...
Actually, I am passing XML from the trigger. This XML consists of data from the Inserted and deleted virtual tables.
I dont know how will I embed the value of COLUMNS_UPDATED() in the XML so that I can pass to the service broker in a single message. I dont want to send an exclusive message for COLUMNS_UPDATED(). It will be a mess (I think...).Thats all for the sending episode...
At the receiver end, I transform the inserted and deleted record(s) from XML back into table. AND the Receiver End is the
SEPARATE DATABASE 🙂 .
But I do have the database name of the sender, and I can peek into the sender database for the schema of the table whose data is sent.
And At this point, I need to compare the transformed tables.
Actually, I do have the idea in my mind and also implemented it and it working FINE, but I am a little upset by "RBAR" methodology. I am trying to find a better and optimized ay to do it.
Currently, I am looping first on the Row number of the Inserted tables (As the number of inserted and deleted will always be same in case of updation). Secondly, I am looping on the columns that I got from the sender database. Comparing each column for each rowid.
But the "RBAR" phenomenon is teasing me. HELP ME...
Regards,
Atif Sheikh
June 30, 2010 at 4:39 am
I have two tables which contain the same records but those records are entered by different users and i want to calculate similairty between same records(by every comapring every single field) entered by different users, and if a field in one table is similar to corresponding field in the second table then i want to store 1 in a column in third table (Table_C).
For Example I have two tables Table_A and Table_B as below:
Table_A
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 123 CS 345
3 223 TE 190
Table_B
----------------------
RecordID StudentID Dept BookID
1 123 CS 456
2 223 TE 345
3 223 TE 190
and i have another table Table_C in which is store the similarity between the similar fields in Table_A and Table_B. The Sturcutre of the table is as follows:
Table_C
----------------------
Sim_RecordID Sim_StudentID Sim_Dept SimBookID
1 1 1 1
1 0 0 1
1 1 1 1
Note: I want to comapre only those records in Table_A and Table_B where RecordID and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. I hope i have clearly defined my probelm. any help would be appreciated.
June 30, 2010 at 5:39 am
1. You should have started a new thread.
2. Please come up with the DDL statements and Insert statements of the sample data.
3. I hope this will help you;
Declare @vTable1 Table (id int, StudentID int, Dept varchar(10),BookID int)
Declare @vTable2 Table (id int, StudentID int, Dept varchar(10),BookID int)
Insert into @vTable1
Select 1,123,'CS',465 Union All
Select 2,123,'CS',345 Union All
Select 3,223,'TE',190
Insert into @vTable2
Select 1,123,'CS',465 Union All
Select 2,223,'TE',345 Union All
Select 3,223,'TE',190
Select case when a.ID = b.ID then 1 else 0 end as Sim_RecordID,
case when a.StudentID = b.StudentID then 1 else 0 end as Sim_StudentID,
case when a.Dept = b.Dept then 1 else 0 end as Sim_Dept,
case when a.BookID = b.BookID then 1 else 0 end as Sim_BookID
From @vTable1 a
Inner Join @vTable2 b on b.ID = a.ID
Atif Sheikh
June 30, 2010 at 6:03 am
assalam o alaikum
thank you very much for such rapid response. Is there any other way to do this without explicitly mentioning the names of the fields becuase i have about 50 fields in one table, and also i want to do this for every other pair of similar tables so i want to make a generic query/stored procedure for this purpose.
PS: I tried to find the link of posting new thread but could not find one.
Regards,
Kamran
June 30, 2010 at 6:32 am
WS,
then you should go for dynamic sql;
here is an example
Declare @vsql varchar(max)
Declare @vCols varchar(max)
Create Table vTable1 (id int, StudentID int, Dept varchar(10),BookID int)
Create Table vTable2 (id int, StudentID int, Dept varchar(10),BookID int)
Insert into vTable1
Select 1,123,'CS',465 Union All
Select 2,123,'CS',345 Union All
Select 3,223,'TE',190
Insert into vTable2
Select 1,123,'CS',465 Union All
Select 2,223,'TE',345 Union All
Select 3,223,'TE',190
-- Get the column names from schema with case statements to get 0 or 1 as result
-- Now, this will depend upon the columns of your actual tables
Select @vCols = Stuff((Select ',case when a.' + [name] + ' = b.' + [name] + ' then 1 else 0 end ' from sys.columns where Object_id = Object_id('vTable1') for XML Path('')),1,1,'')
Select @vCols
-- Concatenate the @vCols with main sql
Set @vsql = ' Select ' + @vCols + ' From vTable1 a
Inner Join vTable2 b on b.ID = a.ID '
Print @vsql
Exec (@vSQL)
Drop table vTable1
Drop table vTable2
Hope, it will help.
May be you would have to make a stored proc to accomplish your task in real time...
June 30, 2010 at 6:33 am
And Click on 'ADD Topic' button on the top to add a new thread.
June 30, 2010 at 10:17 pm
assalam o alaikum
thank you very much for your help.
Regards,
Kamran
June 30, 2010 at 10:30 pm
July 1, 2010 at 12:00 am
assalam o alaikum
now i have tried to explain my problem clearly in a seperate thread. please if you can spare some time to help me out. the link of the thread is
http://www.sqlservercentral.com/Forums/Topic945934-149-1.aspx
Regards,
Kamran
July 1, 2010 at 12:42 am
Kamran,
How would you come to know about which rows are identical...as in your example Row 1 and 3 are identical
Raunak J
July 1, 2010 at 12:49 am
I want to compare only those records in Table_A and Table_B where Recorded and StudentID are same in both tables. i.e. i want a query or simple stored procedure to compare all columns of Table_A with corresponding columns of Table_B where Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID and Store 1 if the fields are similar otherwise store 0 in Table_C in the corresponding field. it is not neccessary that the whole row is identical but i want to compare all the fields of the rows satisfying the criteria Table_A.RecorID = Table_B.RecordID and Table_A.StudentID = Table_B.StudentID
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply