Login or Sign Up to become a member!

LessThanDot

Community Wiki

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Update triggers

From Wiki

Jump to: navigation, search

Below is some code that will show how to test for updated field values in an update trigger. As you can see the IF UPDATE (Column) is true even when the values don’t change. Another thing to keep in mind is that if a value changes from NULL to something else and vice-versa, and you are comparing deleted and inserted tables without using COALESCE or ISNULL it won’t return those rows. Run the code below to see what I mean

create this table

tsqlLine number Off | Hide | Select all

CREATE TABLE TestTrigger (TestID INT IDENTITY,
name VARCHAR(20),
VALUE DECIMAL(12,2) ,
CONSTRAINT chkPositiveValue CHECK (VALUE > 0.00) )

Insert a row

tsqlLine number Off | Hide | Select all

INSERT INTO TestTrigger
SELECT 'SQL',500.23

Create the trigger

tsqlLine number Off | Hide | Select all

CREATE TRIGGER trTest
ON TestTrigger
FOR UPDATE
AS
IF @@ROWCOUNT =0
RETURN
IF UPDATE(VALUE)
BEGIN
SELECT '1', * FROM deleted d JOIN inserted i ON d.testid =i.testid
SELECT '2',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND i.VALUE <> d.VALUE
SELECT '3',* FROM deleted d JOIN inserted i ON d.testid =i.testid
AND COALESCE(i.VALUE,-1) <> COALESCE(d.VALUE,-1)
END
GO

Let's update the value to 100

tsqlLine number Off | Hide | Select all

UPDATE TestTrigger SET VALUE = 100 WHERE testid =1
--we get back all 3 rows

Let's run the same statement

tsqlLine number Off | Hide | Select all

UPDATE TestTrigger SET VALUE = 100 WHERE testid =1
--we get back the first row

Let's really update

tsqlLine number Off | Hide | Select all

UPDATE TestTrigger SET VALUE = 200 WHERE testid =1
--we get back all 3 rows

Let's update with NULL

tsqlLine number Off | Hide | Select all

UPDATE TestTrigger SET VALUE =NULL WHERE testid =1
--we get back rows 1 and 3, row 2 is not returned because it can't compare it

Let's update with NULL again

tsqlLine number Off | Hide | Select all

UPDATE TestTrigger SET VALUE =NULL WHERE testid =1
--we get back row 1

Let's update with 300

tsqlLine number Off | Hide | Select all

UPDATE TestTrigger SET VALUE =300 WHERE testid =1
--we get back rows 1 and 3, row 2 doesn't return because it can't compare NULL to 300

Let's update with 500

tsqlLine number Off | Hide | Select all

UPDATE TestTrigger SET VALUE =500 WHERE testid =1
--we get back all 3 rows

Contributed by: --SQLDenis 03:12, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Pitfalls

Currently 1.71/5
1
2
3
4
5

185 Rating: 1.7/5 (7 votes cast)

Retrieved from "http://wiki.lessthandot.com/index.php/Update_triggers"