Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

t-sql update trigger Expand / Collapse
Author
Message
Posted Wednesday, February 6, 2013 2:39 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
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?
Post #1416742
Posted Wednesday, February 6, 2013 2:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 5:16 AM
Points: 887, Visits: 1,774
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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2

Jeremy Oursler
Post #1416746
Posted Wednesday, February 6, 2013 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1416747
Posted Wednesday, February 6, 2013 4:24 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 25, 2014 11:17 AM
Points: 377, Visits: 446
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
Post #1416763
Posted Thursday, February 7, 2013 8:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:31 PM
Points: 13,207, Visits: 12,688
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1417136
Posted Thursday, February 7, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:48 AM
Points: 12,921, Visits: 32,285
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1417151
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse