SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 sql server 2008 update trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jassie
Constraint Violating Yak Guru

308 Posts

Posted - 02/06/2013 :  16:40:44  Show Profile  Reply with Quote
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

USA
15675 Posts

Posted - 02/06/2013 :  17:24:43  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Go to Top of Page

jassie
Constraint Violating Yak Guru

308 Posts

Posted - 02/06/2013 :  18:22:13  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/06/2013 :  19:33:19  Show Profile  Reply with Quote
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)
			)
Go to Top of Page

jassie
Constraint Violating Yak Guru

308 Posts

Posted - 02/06/2013 :  22:30:49  Show Profile  Reply with Quote
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.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/07/2013 :  05:04:38  Show Profile  Reply with Quote
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)
	))
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/07/2013 :  05:56:22  Show Profile  Reply with Quote

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)


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

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/07/2013 :  06:25:20  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/07/2013 :  06:32:03  Show Profile  Reply with Quote
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/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/07/2013 :  07:04:34  Show Profile  Reply with Quote
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
Go to Top of Page

jassie
Constraint Violating Yak Guru

308 Posts

Posted - 02/07/2013 :  09:33:07  Show Profile  Reply with Quote
The value in start date can be null. Would this make a difference in the sql? Should I be checking for null values?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/07/2013 :  09:36:32  Show Profile  Reply with Quote
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/

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/07/2013 :  09:40:06  Show Profile  Reply with Quote
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.
Go to Top of Page

jassie
Constraint Violating Yak Guru

308 Posts

Posted - 02/07/2013 :  11:05:16  Show Profile  Reply with Quote
Thanks for all your help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.31 seconds. Powered By: Snitz Forums 2000