Using Columns_Updated() in a trigger

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 2000 Forums

SQL Server Development (2000)

Using Columns_Updated() in a trigger

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-30 : 08:24:19

Greg writes "In my scenario I need to be able to tell if C3 OR C4 OR C5 OR C13 OR C17 were updated. If any of these columns were updated, I have to write the deleted record to the history table. Although there may have been many columns updated, how do I determine if just C13 or C17 or C3 has been updated?

I attempted to create a basic update trigger on the table. I received an error stating that it could not read text, ntext or images from the deleted table. When the database was originally created is used the following create table command:

CREATE TABLE [dbo].[Crew_Header] (
[Crew_Seq_No] [numeric](18, 0) IDENTITY (278, 1) NOT NULL ,
[Crew_Work_Date] [datetime] NULL ,
[Shop_Seq_No] [numeric](18, 0) NULL ,
[CMA_Seq_No] [numeric](18, 0) NULL ,
[Status] [varchar] (2) NULL ,
[Sheet_Comment] [text] NULL ,
[Created_By] [varchar] (10) NULL ,
[Creation_Date] [datetime] NULL ,
[Updated_By] [varchar] (10) NULL ,
[Updated_Date] [datetime] NULL ,
[Batch_Transfer_No] [int] NULL ,
[Export_Date] [datetime] NULL ,
[Extract_Date] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

The trigger that I tried to create was:

CREATE TRIGGER updCrewHeader
on Crew_Header
FOR update AS

IF (COLUMNS_UPDATED() = 4)

INSERT INTO Crew_Header_Hist(
Crew_Seq_No ,
Crew_Work_Date ,
Shop_Seq_No ,
CMA_Seq_No ,
Status ,
Sheet_Comment ,
Created_By ,
Creation_Date ,
Updated_By ,
Updated_Date ,
Batch_Transfer_No ,
Export_Date ,
Extract_Date)
SELECT Crew_Seq_No ,
Crew_Work_Date ,
Shop_Seq_No ,
CMA_Seq_No ,
Status ,
Sheet_Comment ,
Created_By ,
Creation_Date ,
Updated_By ,
Updated_Date ,
Batch_Transfer_No ,
Export_Date ,
Extract_Date
FROM deleted
go

Thank You for your time and help

Greg Richardson"

nr
SQLTeam MVY

12543 Posts

Posted - 2002-01-30 : 12:40:30

columns_updated is a bit map.

select @bit = power(2,@col - 1)
will be the bit for any column

to test for the bit

if columns_updated() & @bit > 0

So for your columns
if columns_updated() & (power(2,16) | power(2,12) | power(2,4) | power(2,3) | power(2,2))

There is a bit about using columns updated (including how to use it on more that 32 columns) and creating audit trail triggers at
www.nigelrivett.com
Columns_updated()
and
Generate Trigger

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources