COLUMNS_UPDATED - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

COLUMNS_UPDATED

Author

Topic

harivhn
Starting Member

4 Posts

Posted - 2008-08-20 : 03:21:07

Hi,

Refer to my table structure as shown below:

Base Table:
CREATE TABLE SalesHistory
(
SaleID INT IDENTITY(1,1),
Product VARCHAR(30) NOT NULL,
SaleDate DATETIME,
SalePrice MONEY
)
Audit Table:
CREATE TABLE SalesHistoryAudit_Revised(
AuditID INT IDENTITY(1,1),
SaleID INT,
COLUMN_UPDATED VARCHAR(50),
OLD_VALUE VARCHAR(500),
NEW_VALUE VARCHAR(500),
UPD_DATE DATETIME,
)

I've to maintain the column that got updated and store its old and new value.

I tried to use COLUMNS_UPDATED() approach available in the net manipulate the above audit table but couldn't succeed. My trigger is as follows:

-----------------------------------------------------------------
ALTER TRIGGER tr_SalesHistory ON SalesHistory
FOR UPDATE
AS
BEGIN
DECLARE @FldsUpdated TABLE (COL_ID INT IDENTITY(1,1),[NAME] VARCHAR(100))
DECLARE @ColumnsUpdated VARBINARY(100),@START INT, @MAX_CNT INT,@SQL NVARCHAR(1000),@COLUMN VARCHAR(100)

SET @ColumnsUpdated = COLUMNS_UPDATED()

INSERT INTO @FldsUpdated
SELECT COLUMN_NAME as [NAME]
FROM INFORMATION_SCHEMA.COLUMNS Field
WHERE
TABLE_NAME = 'SalesHistory' AND
sys.fn_IsBitSetInBitmask
(@ColumnsUpdated,COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')) <> 0
SELECT @START=1,@MAX_CNT=MAX(COL_ID) FROM @FLDSUPDATED
WHILE @START<=@MAX_CNT
BEGIN
SELECT @COLUMN=[NAME] FROM @FldsUpdated WHERE [COL_ID]=@START
SET @SQL = 'INSERT INTO SalesHistoryAUDIT_Revised SELECT I.SaleID,'''+@COLUMN+''' AS [COLUMN_UPDATED],D.'+@COLUMN+',I.'+@COLUMN+ ',GETDATE() AS UPD_DATE FROM inserted I INNER JOIN delted D ON I.SALEID=D.SALEID'
-- SELECT * FROM INSERTED
--PRINT @SQL
EXEC SP_EXECUTESQL @SQL
SET @START=@START+1
END
END
-----------------------------------------------------------------

I'm using a dynamic sql to get this done but it seems I cannot use inserted and deleted tables within dynamic sql.

Any help would be greatly appreciated.

Thanks & Regards,

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-20 : 03:36:46

Any error message ? By the way the deleted is spelled wrongly

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources