Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

COLUMNS_UPDATED() and syscolumns Expand / Collapse
Author
Message
Posted Friday, October 31, 2008 3:32 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:40 AM
Points: 56, Visits: 48
Your thoughts regarding this script, its performance and also feel free to optimize/comment...

Thanks,

DECLARE @maxPosi AS INT
DECLARE @realUpdate AS INT
DECLARE @posi AS INT

SET @maxPosi = LEN(COLUMNS_UPDATED())
SET @realUpdate = 0
SET @posi = 1

WHILE (@posi <= @maxPosi)
BEGIN
IF @posi-1 = 0
BEGIN
SET @realUpdate = @realUpdate + CAST(SUBSTRING(COLUMNS_UPDATED(),@posi,1) AS INT)
END
ELSE
BEGIN
IF SUBSTRING(COLUMNS_UPDATED(),@posi,1) > 0
BEGIN
SET @realUpdate = @realUpdate + CAST(SUBSTRING(COLUMNS_UPDATED(),@posi,1) * POWER(2,8*(@posi-1)) AS INT)
END
END
SET @posi = @posi +1
END

SELECT CAST(POWER(2,colorder-1) as int), @realupdate , name FROM syscolumns WHERE id = ANY (SELECT id FROM sysobjects WHERE Name = 'MyTable') AND POWER(2,colorder-1) & @realUpdate > 0

(This is to be used in a trigger)

Thanks again!
Post #595315
Posted Friday, October 31, 2008 3:47 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:10 PM
Points: 4,576, Visits: 8,351
Why do you need to convert COLUMNS_UPDATED() to @realUpdate?
What's wrong with it as it is?

And there is an easier way to do this conversion:
SET @realUpdate = CONVERT(int, COLUMNS_UPDATED() )

But you need to be sure you have less then 32 columns in the table, otherwise it will overflow int value.

But main point remains the same: what do you need it for?
There must be an easier way.
Post #595319
Posted Friday, October 31, 2008 4:08 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:40 AM
Points: 56, Visits: 48
CONVERT(int, COLUMNS_UPDATED() ) does not work

columns_updated is 0xF2FF03
the int value is 15924995
the bin value is 11110010 11111111 00000011
F2 FF 03

But in fact i'm expecting
int -> 262130
bin -> 00000011 11111111 11110010
03 FF F2

if this case the updated columns are in position (non-zero based)
2, 5-18
00000000 00000000 00000010 [2]
00000000 00000000 00010000 [5]
00000000 00000000 00100000 [6]
...
00000001 00000000 00000000 [17]
00000010 00000000 00000000 [18]
Post #595324
Posted Friday, October 31, 2008 5:09 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:10 PM
Points: 4,576, Visits: 8,351
You did not explain why do you need to convert COLUMNS_UPDATED() to @realUpdate.
Post #595338
Posted Friday, October 31, 2008 5:55 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, February 4, 2014 8:40 AM
Points: 56, Visits: 48
I want to inverse the byte sequence so I can use a byte operator with syscolumn.

POWER(2,colorder) returns the byte in the right sequence and not COLUMNS_UPDATED()

The bytes are ordered from left to right. COLUMNS_UPDATED is not compatible with colorder of syscolumns.

That's why I want to reverse the byte sequence
Post #595344
Posted Friday, October 31, 2008 9:40 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, September 12, 2014 11:15 AM
Points: 51, Visits: 229
See if my trigger will help you... it's using the Tally Table, SYSCOLUMNS, and the COLUMNS_UPDATED...

The WHERE CLAUSE are the Columns I want to capture data for, and my CASE STATEMENTS MATCH...

I use this trigger on 20 tables... Some have 30 columns... so being that I am lazy and that changes can happen a lot, I open the table I want like I'm going to edit it, paste that into an excel spreadsheet... I give my columns names a NAMED RANGE, and in vba colde it produces all these for me in milliseconds...

This Trigger is super fast using the combination mention above, all built from the help of this Forum and articles... yah....



ALTER TRIGGER [dbo].[trg_tlkpSegmentGroupSource_AuditUpdates]
ON [dbo].[tlkpSegmentGroupSource]
--Author: John Steinbeck
--Date: 24 Sept 08
--Purpose: This Trigger is designed to capture any column whose value is updated... you will specify what columns you want
FOR UPDATE
AS

---- Declarations
DECLARE @TableName VARCHAR(100)
DECLARE @KeyField VARCHAR(100)
DECLARE @KeyVal VARCHAR(100)
DECLARE @OldVal VARCHAR(500)
DECLARE @NewVal VARCHAR(500)
DECLARE @COL_NAME NVARCHAR(100)
DECLARE @SYSUSER VARCHAR(100)
DECLARE @GETDATE DATETIME
DECLARE @XID VARCHAR(50)

---- Instantiate
SET @TableName = 'tlkpSegmentGroupSource' --Table Name of the Table used for this Trigger CHANGE MY VALUE
SET @KeyField = 'SegmentGroupSourceID'
SELECT @KeyVal = SegmentGroupSourceID FROM INSERTED --CHANGE MY VALUE
SET @GETDATE = CAST(GETDATE() AS DATETIME)
SET @XID= SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER)+1,(LEN(SYSTEM_USER)+1) - CHARINDEX('\',SYSTEM_USER))
SELECT @SYSUSER = USERNAME FROM tblAuthorizations
WHERE XID = @XID

SET @SYSUSER = ISNULL(@SYSUSER, @XID)
-- FOR INSERT AND UPDATE ONLY...
---- Update Audit Fields on Table
Update tlkpSegmentGroupSource
SET UpdatedBy = @SYSUSER, LastUpdate = @GETDATE
WHERE SegmentGroupSourceID IN (SELECT SegmentGroupSourceID FROM Inserted)

-- LOOPING THRU SYSTEM FUNCTION TO SEE WHAT COLUMNS HAVE BEEN UPDATED AND GET THE NAMES... STORE IN TEMP TBL
INSERT INTO tblAuditLog
(TableName, KeyField, KeyValue, UpdatedBy, LastUpdate, ChangedColumn, OldValue, NewValue)
SELECT
@TableName AS TBL
, @KeyField AS KY
, @KeyVal AS KV
, @SYSUSER AS SYS
, @GETDATE AS THEDATE
, OLD.NAME AS COLNAME
, ISNULL(CAST(OLD.OLD_COL_VALUES AS VARCHAR(500)),'') AS OLD
, ISNULL(CAST(NEW.NEW_COL_VALUES AS VARCHAR(500)),'') AS NEW
FROM (
SELECT
COLS.N
, (CASE COLS.N
WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
END) OLD_COL_VALUES
, COLS.NAME
FROM
(
SELECT DISTINCT *
FROM DELETED
[Wink] D
CROSS Join
(
SELECT
S.NAME, T.N AS N
FROM
DBO.TALLY T INNER JOIN SYSCOLUMNS S
ON T.N = S.COLID
WHERE ((N IN (2, 3, 4, 5, 6)) -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
AND ID = object_id(@TableName))
AND (CASE
WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
THEN 1
ELSE 0
End
[Wink] = 1
[Wink] COLS
[Wink] OLD INNER JOIN
(
SELECT
COLS.N
, (CASE COLS.N
WHEN 2 THEN CAST([SegmentGroupID] AS VARCHAR(4))
WHEN 3 THEN CAST([SourceID] AS VARCHAR(4))
WHEN 4 THEN CAST([groupJOINS] AS VARCHAR(500))
WHEN 5 THEN CAST([groupWHERE] AS VARCHAR(500))
WHEN 6 THEN CAST([groupSourceTitle] AS VARCHAR(100))
END) NEW_COL_VALUES
, COLS.NAME
FROM
(
SELECT DISTINCT *
FROM INSERTED
[Wink] I
CROSS Join
(
SELECT
S.NAME, T.N AS N
FROM
DBO.TALLY T INNER JOIN SYSCOLUMNS S
ON T.N = S.COLID
WHERE ((N IN (2, 3, 4, 5, 6)) -- THIS IS YOUR NEW LOOP OF COLUMNS YOU WANT MONITORED
AND ID = object_id(@TableName))
AND (CASE
WHEN (SUBSTRING(COLUMNS_UPDATED(), 1 + ROUND(((T.N - 1)) / 8, 0), 1) & POWER (2, ((T.N - 1)) % 8) <> 0 )
THEN 1
ELSE 0
End
[Wink] = 1
[Wink] COLS
[Wink] NEW
ON OLD.N = NEW.N


Post #595383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse