Post reply

Trigger to capture data before and after an update

  • April 6, 2012 at 12:48 pm

    #253742

    Hi, I have been charged with the task of sending an Email any time a table has been updated.

    So, I have put this together (easy peasy):

    IF EXISTS (SELECT name FROM sysobjects

    WHERE name = 'Table1_trg' AND type = 'TR')

    DROP TRIGGER Table1_trg

    GO

    CREATE TRIGGER Table1_trg

    ON Table1

    FOR INSERT, UPDATE, DELETE

    AS

    If db_name() = 'Database1'

    Begin

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'Default',

    @recipients = 'xxxx@xxx.com',

    @body = 'Database1.Table1 has been changed',

    @subject = 'Table1 alert!' ;

    END

    GO

    Now, of course, in the body of the Email, they want the values in the table from-to. I am not entirely sure how to handle that task? Specifically, the before image of the data.

    Any assistance would be greatly appreciated.

    Kind regards,

    Laura

  • April 6, 2012 at 12:50 pm

    #1470231

    You can query the Inserted and Deleted tables within the trigger, but I'm not sure this is a good solution. How often are records added/modified in this table. The trigger could cause performance issues

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • April 6, 2012 at 12:55 pm

    #1470238

    Mike01 (4/6/2012)

    You can query the Inserted and Deleted tables within the trigger, but I'm not sure this is a good solution. How often are records added/modified in this table. The trigger could cause performance issues

    The table is small (few records) and static (for the most part). A batch process is supposed to be the only thing that updates it. However, someone (or process) has updated and caused problems. Hence this request by the users.

    I check out those articles. But based on the table size and change frequency, we decided a trigger would make sense.

  • April 6, 2012 at 12:57 pm

    #1470241

    here's an example i put together for a different post;

    it's getting inserted items, putting them into a comma delimited string(because of course the trigger could have multiple rows!)

    for your trigger, why do you need to check the db_name()? same code copied to DEV / QA?

    it seemed obvious that the trigger, and it's parent table can only exist in a single database at a time, so i wasn't sure why.

    the code below assumes one "key" column; if you are going to have pairs of columns, or all rows affected, the solutions gonna be different.

    CREATE TABLE WHATEVER(

    WHATEVERID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

    DESCRIP VARCHAR(30)

    )

    GO

    ALTER TRIGGER TR_WHATEVER_NOTIFICATIONS

    ON WHATEVER FOR INSERT

    AS

    BEGIN

    SET NOCOUNT ON

    --gather the information, making sure you get it from the INSERTED virtual table, and not the full table

    DECLARE @CAPTUREDSTRING VARCHAR(max)

    --In this example i want a comma delimited list of important facts about what was inserted.

    --using the xml technique to make my comma delimited string.

    SELECT @CAPTUREDSTRING = [Skills]

    FROM (

    SELECT TOP 1 GETDATE()As TheDate,stuff(( SELECT ',' + DESCRIP

    FROM INSERTED s2

    --WHERE s2.WHATEVERID= s1.WHATEVERID --- must match GROUP BY below

    WHERE 1 = 1

    ORDER BY DESCRIP

    FOR XML PATH('')

    ),1,1,'') as [Skills]

    FROM INSERTED s1

    GROUP BY s1.WHATEVERID --- without GROUP BY multiple rows are returned

    ORDER BY s1.WHATEVERID) myAlias

    --now email the results.

    declare @body1 varchar(4000)

    set @body1 = 'New Item Notification on the Whatever Table '

    + CONVERT( VARCHAR( 20 ), GETDATE(), 113 )

    + '

    <P> The following new items were inserted into the table:<P>'

    + @CAPTUREDSTRING

    + '

    '

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='Default Mail Account',

    @recipients='lowell@somedomain.com',

    @subject = 'New Item Notification',

    @body = @body1,

    @body_format = 'HTML'

    END --TRIGGER

    GO

    INSERT INTO WHATEVER(DESCRIP)

    SELECT 'APPLES' UNION

    SELECT 'ORANGES' UNION

    SELECT 'BANANAS' UNION

    SELECT 'GRAPES' UNION

    SELECT 'CHERRIES' UNION

    SELECT 'KIWI'

    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!

  • April 6, 2012 at 1:00 pm

    #1470243

    Lowell (4/6/2012)

    here's an example i put together for a different post;

    it's getting inserted items, putting them into a comma delimited string(because of course the trigger could have multiple rows!)

    for your trigger, why do you need to check the db_name()? same code copied to DEV / QA?

    it seemed obvious that the trigger, and it's parent table can only exist in a single database at a time, so i wasn't sure why.

    the code below assumes one "key" column; if you are going to have pairs of columns, or all rows affected, the solutions gonna be different.

    Thank you. I will take a look at your code and see if I can apply it.

    Yes, we copy the database frequenty for Development and thought the user wouldn't appreciate the additional Emails.

  • April 6, 2012 at 1:11 pm

    #1470252

    how many columns are"critical" that you have to track for changes in the table to be emailed?

    It would be pretty easy to modify my example, but i'd like to see the table definition, or at leas which columns must be tracked to make a solid,working example.

    you could put put something like

    PK 43: Firstname: 'Joe' ->> 'Joseph', Middle: '' --> 'M', LastName: '' --> 'Smith'

    PK 69: Middle: '' --> 'S'

    PK 117: LastName: 'Jones' --> 'Zeta-Jones'

    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!

  • April 6, 2012 at 1:15 pm

    #1470253

    Lowell (4/6/2012)

    how many columns are"critical" that you have to track for changes in the table to be emailed?

    It would be pretty easy to modify my example, but i'd like to see the table definition, or at leas which columns must be tracked to make a solid,working example.

    you could put put something like

    PK 43: Firstname: 'Joe' ->> 'Joseph', Middle: '' --> 'M', LastName: '' --> 'Smith'

    PK 69: Middle: '' --> 'S'

    PK 117: LastName: 'Jones' --> 'Zeta-Jones'

    There's 11 rows. Very small table.

    CREATE TABLE [dbo].[bcx_CycleDateRefTbl_ext](

    [LoadCommandID] [int] NULL,

    [PublicID] [varchar](20) NOT NULL,

    [UpdateUserID] [int] NULL,

    [CycleDate] [datetime] NULL,

    [UpdateTime] [datetime] NOT NULL,

    [CreateUserID] [int] NULL,

    [BeanVersion] [int] NULL,

    [Retired] [int] NOT NULL,

    [CreateTime] [datetime] NOT NULL,

    [MonthEndCycleDate] [datetime] NULL,

    [ID] [int] NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

  • April 6, 2012 at 1:31 pm

    #1470258

    ok, one of the things you'll run into first is permissions to send mail inside the trigger.

    EVERY user that can access the table will need to a user in the msdb database, and part of the DatabaseMailUserRole;

    if a user is not a sysadmin, then the trigger will fail and rollback because of the permissions issue (cross database) to try and send the mail.

    it might be easier to simply log changes, complete with Audit information, and check the table occasionally.

    untested prototype:

    CREATE TABLE [dbo].[BCX_CYCLEDATEREFTBL_EXT_Audit] (

    [InsertedorUpdated] VARCHAR(12) NULL,

    [LOADCOMMANDID] INT NULL,

    [PUBLICID] VARCHAR(20) NOT NULL,

    [UPDATEUSERID] INT NULL,

    [CYCLEDATE] DATETIME NULL,

    [UPDATETIME] DATETIME NOT NULL,

    [CREATEUSERID] INT NULL,

    [BEANVERSION] INT NULL,

    [RETIRED] INT NOT NULL,

    [CREATETIME] DATETIME NOT NULL,

    [MONTHENDCYCLEDATE] DATETIME NULL,

    [ID] INT NOT NULL,

    [EVENTDATE] DATETIME NOT NULL,

    [DBNAME] NVARCHAR(128) NULL,

    [HOSTNAME] NVARCHAR(128) NULL,

    [APPLICATIONNAME] NVARCHAR(128) NULL,

    [PROCEDURENAME] NVARCHAR(128) NULL,

    [USERID] SMALLINT NULL,

    [UNSERNAME] NVARCHAR(128) NULL,

    [SUSERID] INT NULL,

    [SUSERNAME] NVARCHAR(128) NULL,

    [IS_SERVERADMIN_SYSADMIN] INT NULL,

    [IS_DB_OWNER] INT NULL,

    [IS_DDL_ADMIN] INT NULL,

    [IS_DB_DATAREADER] INT NULL,

    [ORIGINAL_LOGIN] NVARCHAR(4000) NULL,

    CONSTRAINT [PK__auditngSomeTable] PRIMARY KEY CLUSTERED (ID))

    GO

    --a more restrictive role, but one that everyone belongs to would be better.

    GRANT INSERT ON BCX_CYCLEDATEREFTBL_EXT_Audit TO PUBLIC

    GO

    CREATE TRIGGER TR_AUDIT_SOMETABLE

    ON [dbo].[bcx_CycleDateRefTbl_ext]

    FOR UPDATE

    AS

    BEGIN

    SET NOCOUNT ON

    INSERT INTO [dbo].[BCX_CYCLEDATEREFTBL_EXT_Audit]

    SELECT

    'INSERTED',

    INSERTED.*,

    getdate() as EventDate,

    DB_NAME() As dbname,

    HOST_NAME() As hostname,

    APP_NAME() as applicationname,

    OBJECT_NAME(@@PROCID) as procedurename,

    USER_ID() as userid,

    USER_NAME() as unsername,

    SUSER_ID() as suserid,

    SUSER_SNAME() as susername,

    IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

    IS_MEMBER('db_owner') AS [Is_DB_owner],

    IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

    IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

    ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]

    FROM INSERTED

    INSERT INTO [dbo].[BCX_CYCLEDATEREFTBL_EXT_Audit]

    SELECT

    'DELETED',

    DELETED.*,

    getdate() as EventDate,

    DB_NAME() As dbname,

    HOST_NAME() As hostname,

    APP_NAME() as applicationname,

    OBJECT_NAME(@@PROCID) as procedurename,

    USER_ID() as userid,

    USER_NAME() as unsername,

    SUSER_ID() as suserid,

    SUSER_SNAME() as susername,

    IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

    IS_MEMBER('db_owner') AS [Is_DB_owner],

    IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

    IS_MEMBER('db_datareader') AS [Is_DB_Datareader],

    ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN]

    FROM DELETED

    END

    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!

  • April 6, 2012 at 1:38 pm

    #1470261

    The permissions shouldn't be an issue. The application uses one ID, I can easily add to the DatabaseMailUserRole.

  • April 6, 2012 at 1:50 pm

    #1470266

    Your table example deals with inserted and deleted rows. I am looking for the before image and the after image of the data on an update.

    How would I isolate the record before it was updated?

  • April 6, 2012 at 1:54 pm

    #1470269

    nevermind my above (stupid) question. I see how it works. Thank you!

  • April 6, 2012 at 1:56 pm

    #1470270

    I may be a little late on this one but I would suggest you use @@servername instead of db_name(). This has bitten me a couple times in the past. You take a prod backup and restore it to a server for testing and use the prod name...oops. 😀 Instead by checking the server name you know it won't send if it is not on the prod box.

    _______________________________________________________________

    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/

  • April 6, 2012 at 2:00 pm

    #1470274

    Sean Lange (4/6/2012)

    I may be a little late on this one but I would suggest you use @@servername instead of db_name(). This has bitten me a couple times in the past. You take a prod backup and restore it to a server for testing and use the prod name...oops. 😀 Instead by checking the server name you know it won't send if it is not on the prod box.

    Thanks for the suggestion. In this case, db_name is appropriate. The restoration server is the same as the original. But I will certainly keep that in mind for future use.

  • April 6, 2012 at 2:12 pm

    #1470278

    ok based on your table scheme you posted, this correctly builds a string and prints it, listing only columns that CHANGED, along with some hopefully helpful audit information.

    I'll leave it to you to send the @capturedString via email:

    CREATE TRIGGER TR_AUDIT_SOMETABLEV2

    ON [dbo].[bcx_CycleDateRefTbl_ext]

    FOR UPDATE

    AS

    BEGIN

    SET NOCOUNT ON

    --gather the information, making sure you get it from the INSERTED virtual table, and not the full table

    DECLARE @CAPTUREDSTRING VARCHAR(max)

    SET @CAPTUREDSTRING = ''

    SET @CAPTUREDSTRING =

    'EventDate:' + CONVERT(VARCHAR(30),getdate(),121) + CHAR(13) + CHAR(10)

    + 'dbname:' + CONVERT(VARCHAR(30),+ DB_NAME()) + CHAR(13) + CHAR(10)

    + 'hostname:' + CONVERT(VARCHAR(30),+ HOST_NAME()) + CHAR(13) + CHAR(10)

    + 'applicationname:' + CONVERT(VARCHAR(30),+ APP_NAME()) + CHAR(13) + CHAR(10)

    + 'procedurename:' + CONVERT(VARCHAR(30),+ ISNULL(OBJECT_NAME(@@PROCID),'')) + CHAR(13) + CHAR(10)

    + 'userid:' + CONVERT(VARCHAR(30),+ USER_ID() ) + CHAR(13) + CHAR(10)

    + 'unsername:' + CONVERT(VARCHAR(30),+ USER_NAME() ) + CHAR(13) + CHAR(10)

    + 'suserid:' + CONVERT(VARCHAR(30),+ SUSER_ID()) + CHAR(13) + CHAR(10)

    + 'susername:' + CONVERT(VARCHAR(30),+ SUSER_SNAME() ) + CHAR(13) + CHAR(10)

    + 'ORIGINAL_LOGIN:' + CONVERT(VARCHAR(30),+ ORIGINAL_LOGIN()) + CHAR(13) + CHAR(10)

    + @CAPTUREDSTRING

    PRINT @CAPTUREDSTRING

    SELECT @CAPTUREDSTRING = @CAPTUREDSTRING + 'ID:' + CONVERT(VARCHAR,INSERTED.ID) + ' : :'

    + CASE WHEN INSERTED.LoadCommandID <> DELETED.LoadCommandID THEN 'LoadCommandID: [' + ISNULL(CONVERT(VARCHAR,DELETED.LoadCommandID),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.LoadCommandID),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.PublicID <> DELETED.PublicID THEN 'PublicID: [' + ISNULL(CONVERT(VARCHAR,DELETED.PublicID),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.PublicID),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.UpdateUserID <> DELETED.UpdateUserID THEN 'UpdateUserID: [' + ISNULL(CONVERT(VARCHAR,DELETED.UpdateUserID),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.UpdateUserID),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.CycleDate <> DELETED.CycleDate THEN 'CycleDate: [' + ISNULL(CONVERT(VARCHAR,DELETED.CycleDate),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.CycleDate),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.UpdateTime <> DELETED.UpdateTime THEN 'UpdateTime: [' + ISNULL(CONVERT(VARCHAR,DELETED.UpdateTime),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.UpdateTime),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.CreateUserID <> DELETED.CreateUserID THEN 'CreateUserID: [' + ISNULL(CONVERT(VARCHAR,DELETED.CreateUserID),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.CreateUserID),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.BeanVersion <> DELETED.BeanVersion THEN 'BeanVersion : [' + ISNULL(CONVERT(VARCHAR,DELETED.BeanVersion ),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.BeanVersion ),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.Retired <> DELETED.Retired THEN 'Retired : [' + ISNULL(CONVERT(VARCHAR,DELETED.Retired ),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.Retired ),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.CreateTime <> DELETED.CreateTime THEN 'CreateTime : [' + ISNULL(CONVERT(VARCHAR,DELETED.CreateTime ),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.CreateTime ),'') + ']' ELSE '' END

    + CASE WHEN INSERTED.MonthEndCycleDate <> DELETED.MonthEndCycleDate THEN 'MonthEndCycleDate : [' + ISNULL(CONVERT(VARCHAR,DELETED.MonthEndCycleDate ),'') + ']-->[' + ISNULL(CONVERT(VARCHAR,INSERTED.MonthEndCycleDate ),'') + ']' ELSE '' END

    FROM INSERTED

    INNER JOIN DELETED ON INSERTED.ID = DELETED.ID

    PRINT @CAPTUREDSTRING

    --the email can now be sent.

    END

    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!

  • April 6, 2012 at 2:15 pm

    #1470280

    Lowell, thank you very much! I am investigating the table option as well. Truly appreciate your help!

Viewing 15 posts - 1 through 15 (of 15 total)

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy