HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

In a Trigger, can I determine if a column was explicitly set to a values or not mentioned in update statement?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cantriggervaluesupdatecolumnstatementmentionedexplicitlydeterminewas

Problem

Given a Table with a column col1 like this:

create table MyTest (
    col1 int NULL,
    col2 decimal(7, 2) NULL
);

insert into MyTest values ( 1, 1.1);


Is there a way for a trigger for update to decide which of the following update statements was executed ?

A:

update MyTest set col1 = 3;


B:

update MyTest set col1 = 3, col2 = 1.1;

Solution

It makes a difference if this is going to be a set-based trigger, or if you are designing it to handle only one row at a time, but this should get you pointed in the right direction.

IF
UPDATE(col1)
AND UPDATE(col2)
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3 AND col2 = 1.1)
BEGIN
    DO STUFF
END

IF 
UPDATE(col1) 
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3)
BEGIN
    DO STUFF
END

Code Snippets

IF
UPDATE(col1)
AND UPDATE(col2)
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3 AND col2 = 1.1)
BEGIN
    DO STUFF
END

IF 
UPDATE(col1) 
AND EXISTS(SELECT * FROM inserted WHERE col1 = 3)
BEGIN
    DO STUFF
END

Context

StackExchange Database Administrators Q#1604, answer score: 7

Revisions (0)

No revisions yet.