Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » t-sql update trigger
Add to briefcase

t-sql update trigger Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
wendy elizabeth
Posted Wednesday, February 06, 2013 2:39 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159
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
CapnHector
Posted Wednesday, February 06, 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, May 17, 2013 10:07 AM
Points: 935, Visits: 1,709
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
Lowell
Posted Wednesday, February 06, 2013 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 11,645, Visits: 27,732
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
wendy elizabeth
Posted Wednesday, February 06, 2013 4:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:58 AM
Points: 137, Visits: 159
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
Sean Lange
Posted Thursday, February 07, 2013 8:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 1:15 PM
Points: 8,618, Visits: 8,259
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
Post #1417136
Lowell
Posted Thursday, February 07, 2013 9:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 11,645, Visits: 27,732
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

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.