October 31, 2008 at 3:32 pm
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!
October 31, 2008 at 3:47 pm
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.
_____________
Code for TallyGenerator
October 31, 2008 at 4:08 pm
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]
October 31, 2008 at 5:09 pm
You did not explain why do you need to convert COLUMNS_UPDATED() to @realUpdate.
_____________
Code for TallyGenerator
October 31, 2008 at 5:55 pm
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
October 31, 2008 at 9:40 pm
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-2 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-2 = 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-2
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-2 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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply