SQL Trigger for multiple columns - Insert / update

Question

Post reply

SQL Trigger for multiple columns - Insert / update

Minnu

SSCertifiable

Points: 6451
More actions
December 5, 2012 at 3:00 am

#265744

hi Team,
I want to create a trigger, that that should fire when ever particular columns are updated/inserted.
am using below query.. is it correct way.
CREATE TRIGGER [TRG_TESTING]
ON TABLE_NAME
AFTER INSERT,UPDATE
AS
SET NOCOUNT ON
IF (UPDATE (Col1,Col2,Col3) OR
(INSERT (Col1,Col2,Col3)
DECLARE
@Var1 varchar(max),
@ID INT
---
---
--
.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113445 More actions · Answer 1

Look at the COLUMNS_UPDATED() clause

http://msdn.microsoft.com/en-us/library/765fde44-1f95-4015-80a4-45388f18a42c

toddasd SSCertifiable Points: 6298 More actions · Answer 2

UPDATE takes only one column. There is no similar 'INSERT' function; UPDATE checks for both.

Your line would change to

IF (UPDATE(Col1) OR UPDATE(Col2) OR UPDATE(Col3))

You could also use COLUMNS_UPDATED, but that may be confusing with the bit mask.

Edit: removed quote

______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.

Lowell SSC Guru Points: 323504 More actions · Answer 3

I think your trigger model needs to look more like this.

because triggers handle multiple rows in SQL server, you should never declare a variable in a trigger, because it makes you think of one row/one value, instead of the set.

there are exceptions of course, but it's a very good rule of thumb.

also note the UPDATE function doesn't tell you the VALUE changed on a column...only whether the column was included int eh column list for insert/update. so if it was updated to the exisitng value (and a lot of data layers will do that automatically) it's a false detection of a change.

CREATE TRIGGER [TRG_TESTING]

ON TABLE_NAME

AFTER INSERT,UPDATE

AS

SET NOCOUNT ON

INSERT INTO SomeTrackingTable(ColumnList)

SELECT ColumnList

FROM INSERTED

LEFT OUTER JOIN DELETED

ON INSERTED.SomePrimaryKey = DELETED.SomePrimaryKey

WHERE DELETED.SomePrimaryKey IS NULL --inserted only

OR (INSERTED.SpecificColumn <> DELETED.SpecificColumn) --this column changed...so we need to log it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

This can be found in BOL:

1) IF UPDATE(b) OR UPDATE(c) ...

2) IF ( COLUMNS_UPDATED() & 2 = 2 )

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service