February 6, 2013 at 2:39 pm
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?
February 6, 2013 at 2:54 pm
Can you post the DDL for the tables and a couple of sample rows and what you would like inserted into the logging table?
This information will help when writing the trigger.
If you need a how to on posting DDL and Sample data check out the first link in my signature.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
February 6, 2013 at 2:59 pm
roughly, the trigger is going to look something like this, but the DDL asked for in the post above would make it definitive:
we don't know what columns you want to audit, or the PK of the table. i hope you can figure out the changes required to my model here:
CREATE TRIGGER TR_TrackChangesInMain ON MAIN
FOR UPDATE
AS
INSERT INTO AuditTable(ColumnList)
SELECT ColumnList
FROM INSERTED
INNER JOIN DELETED ON INSERTED.ID = DELETED.ID
WHERE ISNULL(INSERTED.start_date,'1900-01-01') <> ISNULL(DELETED.start_date,'1900-01-01')
Lowell
February 6, 2013 at 4:24 pm
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].[eRPT_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
February 7, 2013 at 8:55 am
wendy elizabeth (2/6/2013)
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].[eRPT_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
That looks like it should work. I would however recommend that you not use such a generic name for your trigger. If you name your trigger "update_trigger" you can't have another trigger with that name in the database. A common naming convention for this would be something like tr_Transaction_Tracking_Update. That identifies the trigger clearly and prevents naming collisions with other triggers on other tables.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 7, 2013 at 9:04 am
also, you want to bulletproof the comparison if the value for the Start_Date is changing from null to not null: that's not necessary if the field is not-nullable to begin with, so it depends on the table DDL.
i.Start_Date<>d.Start_Date
ISNULL(i.Start_Date,'1900-01-01') <> ISNULL(d.Start_Date,'1900-01-01')
Lowell
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply