sql server 2008 update 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 2008 Forums

Transact-SQL (2008)

sql server 2008 update trigger

Author

Topic

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-02-06 : 16:40:44

In a sql server 2008 r2 database, I am working with triggers for the first time. In an update trigger, I want to add a row to an audit table if the value of start_date changes in a table called 'main'.

Thus can you show me the sql on how to accomplish this goal?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2013-02-06 : 17:24:43

CREATE TRIGGER trg_audit_start_date ON main FOR UPDATE AS
SET NOCOUNT ON
INSERT audit_main(row_id, old_start_date, new_start_date)
SELECT i.row_id, d.start_date, i.start_date
FROM inserted i
INNER JOIN deleted d ON i.row_id=d.row_id
WHERE i.start_date<>d.start_date

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-02-06 : 18:22:13

I changed my trigger to look like the following but it is not working. There are no errors, but the audit table is updated sporadically.

I basically want a row in the audit table when the start date changes.

USE [DEV]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[update_trigger]ON [dbo].[Transaction_Tracking]
AFTER UPDATE
AS
BEGIN
INSERT
INTO [dbo].[Transaction_Audit]
( Package_ID, Received_Date, Start_Date,Operation, TriggerTable)
SELECT i.Package_ID, i.Received_Date, i.Start_Date,'U', 'I'
FROM inserted i
INNER JOIN deleted d ON i.Track_ID=d.Track_ID
WHERE (SUBSTRING(i.Package_ID,1,3) = 'RVW' or SUBSTRING(d.Package_ID,1,3) = 'RVW')
and i.Start_Date<>d.Start_Date
;
END
;
GO

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-06 : 19:33:19

Couple of things to keep in mind.

1. If your update includes the PK column (Track_ID in your case), i.e., if you are changing Track_Id in the update, the inner join will yield no row for that case. So you would need to use one of the OUTER JOINs depending on your needs.

2. If Start_Date can be null, you need to handle that. NULL <> NULL is false, NULL <> Anything Else is false. So if old value is null or if new value is null, (as will certainly the case for inserts and deletes), they will not be picked up unless you handle that explicitly.

I would write it something like this (not tested or debugged, just psuedo code)

INSERT INTO [dbo].[Transaction_Audit]
  (
    Package_ID,
    Received_Date,
    START_DATE,
    Operation,
    TriggerTable
  )
SELECT i.Package_ID,
       i.Received_Date,
       i.Start_Date,
       'U',
       'I'
FROM   INSERTED i
       LEFT JOIN DELETED d
            ON  i.Track_ID = d.Track_ID
			AND -- moving the where clause to the join condition
			( 
			   SUBSTRING(i.Package_ID, 1, 3) = 'RVW'
			   OR SUBSTRING(d.Package_ID, 1, 3) = 'RVW'
			)
			AND NOT
			(
				(i.Start_Date <> d.Start_Date)
				OR
				(i.Start_Date IS NULL AND d.Start_Date IS NULL)
			)

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-02-06 : 22:30:49

I have the following additional questions:
1. the statement, "(i.Start_Date IS NULL AND d.Start_Date IS NULL)"
shouldn't this be
"(i.Start_Date IS NULL AND d.Start_Date IS NOT NULL)"
2. I am not changing the track_id key field this is the identity seed for the field.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 05:04:38

Since PK cannot change, you don't need the left join. As for the where clause, what I really meant to do was to pick up the cases that you DO NOT want to audit, and then apply a NOT on it. The cases you do not want to audit are 1) i.Start_Date = d.Start_Date 2) i.Start_Date is NULL and d.Start_Date is NULL. So, rewriting the query:

INSERT INTO [dbo].[Transaction_Audit]
  (
    Package_ID,
    Received_Date,
    START_DATE,
    Operation,
    TriggerTable
  )
SELECT i.Package_ID,
       i.Received_Date,
       i.Start_Date,
       'U',
       'I'
FROM   INSERTED i
       INNER JOIN DELETED d
            ON  i.Track_ID = d.Track_ID
WHERE
	( 
	   SUBSTRING(i.Package_ID, 1, 3) = 'RVW'
	   OR SUBSTRING(d.Package_ID, 1, 3) = 'RVW'
	)
	AND (NOT
	(
		(i.Start_Date = d.Start_Date)
		OR
		(i.Start_Date IS NULL AND d.Start_Date IS NULL)
	))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 05:56:22

[code]
INSERT INTO [dbo].[Transaction_Audit]
(
Package_ID,
Received_Date,
START_DATE,
Operation,
TriggerTable
)
SELECT i.Package_ID,
i.Received_Date,
i.Start_Date,
'U',
'I'
FROM INSERTED i
INNER JOIN DELETED d
ON i.Track_ID = d.Track_ID
AND i.Package_ID = d.Package_ID
WHERE i.Package_ID LIKE 'RVW%'
AND COALESCE(i.Start_Date,-1) <> COALESCE(d.Start_Date,-1)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 06:25:20

quote:
Originally posted by visakh16

....
AND COALESCE(i.Start_Date,-1) <> COALESCE(d.Start_Date,-1)

As a general rule, I don't like to use the COALESCE function in this manner to handle null comparisons. While very unlikely in this case, December 31, 1899 might be a valid date for this business, or perhaps someone might have decided to use that date as an indicator of some specific type of activity.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 06:32:03

quote:
Originally posted by James K

quote:
Originally posted by visakh16

....
AND COALESCE(i.Start_Date,-1) <> COALESCE(d.Start_Date,-1)

As a general rule, I don't like to use the COALESCE function in this manner to handle null comparisons. While very unlikely in this case, December 31, 1899 might be a valid date for this business, or perhaps someone might have decided to use that date as an indicator of some specific type of activity.

I just gave it as an example

based on your business rules (which you should obviously be aware of) you can select a corresponding invalid value (may be 1753-01-01 or similar values)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 07:04:34

And hence my weariness about using it - people are not always aware of the rules, or someone may decide (perhaps sometime in the future) that 1753-01-01 is a good indicator for packages that were shipped and were delivered to the wrong address.

I am not suggesting that any issue is likely in this specific case; just that I am weary about it out of an abundance of caution. It reminds me of the movie "Princess Bride" where Vizzini is in a predicament trying to decide which cup holds the poisoned wine, is it 12/31/1899 or 1/1/1753, or is it something else: http://www.youtube.com/watch?v=U_eZmEiyTo0

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-02-07 : 09:33:07

The value in start date can be null. Would this make a difference in the sql? Should I be checking for null values?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-07 : 09:36:32

quote:
Originally posted by jassie

The value in start date can be null. Would this make a difference in the sql? Should I be checking for null values?

Thats what code i as well as James suggested does

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-07 : 09:40:06

No, jassie. The code I posted and the one Visakh posted both should handle null dates correctly. The discussion we were having was mostly academic, which you most likely wont run into unless you had start dates in the 19th century. The code Visakh posted is a little easier to read, so go with that.

jassie
Constraint Violating Yak Guru

332 Posts

Posted - 2013-02-07 : 11:05:16

Thanks for all your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources