t-sql update trigger

  • 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?

  • 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[/url] 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[/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]

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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

  • 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/

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply