| Author |
 Topic  |
|
|
jassie
Posting Yak Master
114 Posts |
 Posted - 02/06/2013 : 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
USA
15557 Posts |
Posted - 02/06/2013 : 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
Posting Yak Master
114 Posts |
Posted - 02/06/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/06/2013 : 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
Posting Yak Master
114 Posts |
Posted - 02/06/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/07/2013 : 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
India
47023 Posts |
Posted - 02/07/2013 : 05:56:22
|
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/
|
|
|
|
James K
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/07/2013 : 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
India
47023 Posts |
Posted - 02/07/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/07/2013 : 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
Posting Yak Master
114 Posts |
Posted - 02/07/2013 : 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
India
47023 Posts |
Posted - 02/07/2013 : 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
Flowing Fount of Yak Knowledge
1483 Posts |
Posted - 02/07/2013 : 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
Posting Yak Master
114 Posts |
Posted - 02/07/2013 : 11:05:16
|
| Thanks for all your help! |
|
|
| |
Topic |
|
sql server 2008 update trigger
