Post reply

trigger

  • April 18, 2007 at 8:53 am

    #95056

    Hi,

    Bit of a newbie, so be gentle if I'm asking a dumb or frequently asked question.

    I would like to be able to have a trigger that drops a copy of a row of data into a table each time a row is updated in another table. I'm sure you can understand the application of such.

    Hope this makes sense.

    Thanks,

    Martin

  • April 18, 2007 at 10:07 am

    #701162

    CREATE TRIGGER TR_Audit_TblName_A_U ON dbo.TblName

    FOR UPDATE

    AS

    SET NOCOUNT ON

    INSERT INTO dbo.AuditTbl (ColsList)

    Select ColsList FROM dbo.Inserted

     

     

    This would send the new data inserted in the table.  Change Inserted to Deleted to have the old version of the data (before the update).

  • April 18, 2007 at 10:29 am

    #701171

    Hi,

    Thanks for the response. Tried using the deleted option, but got an error:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e37'

    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.Deleted'.

    Thanks,

     

    Martin

  • April 18, 2007 at 1:38 pm

    #701241

    Oops. Remove the dbo. before the deleted table.

  • April 18, 2007 at 4:44 pm

    #701284

    Get the following error when removing dbo. :

    invalid column name "colslist"

     

  • April 18, 2007 at 8:12 pm

    #701309

    You must list the columns you want to insert in the 2nd table.  You must figure this list out yourself or give us all the info so we can build it for you.

  • April 19, 2007 at 12:48 am

    #701335

    duh, assumed colslist was a command to pull all the columns.

    Thanks, will give it a try.

    Martin

  • April 19, 2007 at 6:29 am

    #701404

    Been there, done that.  Welcome to the team .

Viewing 8 posts - 1 through 7 (of 7 total)

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