Results 1 to 7 of 7

Thread: Update Trigger

  1. #1
    Join Date
    Mar 2010
    Location
    Hyderabad
    Posts
    12

    Unanswered: Update Trigger

    Hi,

    I have a table (CMSAddress) which contains some n number of fields, i am updating my details but when i am updating i want to maintain and insert filed names like column name,oldvalue,new value into another table which fields are updated.

    I used trigger for updating but it is working for only one field updation. when i am trying to update 2 fileds the 2nd field is inserting another table.

    My Query is


    CREATE TRIGGER reminder
    ON CMSAddress
    AFTER UPDATE
    AS
    IF ( UPDATE (AddressLine1) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'Address Line1',(select AddressLine1 from deleted),ins.AddressLine1,getdate()
    FROM inserted ins
    END
    ELSE IF ( UPDATE (AddressLine2) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'Address Line2',(select AddressLine2 from deleted),ins.AddressLine2,getdate()
    FROM inserted ins
    END
    GO


    UPDATE CMSAddress
    SET AddressLine1 = 'SECUNDERABAD'
    WHERE AddressId=2653 (for this it is inserting into another table)



    It is not working for two fields AddressLine1,AddressLine2

    UPDATE CMSAddress
    SET AddressLine1 = 'SECUNDERABAD',
    AddressLine2='HYDERABAD'
    WHERE AddressId=2653

    please help me

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What do you mean by it is not working for 2nd insert? Does the update not happen or it does not insert a second line to your audit table? I would think it would insert 1 line to your audit table just fine for AddressLine1. If that is the case, then look at the logic of your trigger, because it is what you have coded with the ELSEIF.
    Dave

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have a look for a general solution at Sql Server. Generic audit trail trigger - Author Nigel Rivett. Or Google for "MSSQL audit trail trigger" for more examples and discussions about the different possible implementations..

    Your solution, by hard coding each and every column, will become hard to maintain when you want to audit tables with a lot of columns.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Mar 2010
    Location
    Hyderabad
    Posts
    12
    Quote Originally Posted by dav1mo View Post
    What do you mean by it is not working for 2nd insert? Does the update not happen or it does not insert a second line to your audit table? I would think it would insert 1 line to your audit table just fine for AddressLine1. If that is the case, then look at the logic of your trigger, because it is what you have coded with the ELSEIF.
    Dave


    yes, it does not insert a second line to my audit table with that column name. How can i check for 2 or more columns and i need that column names which was updated, i need to insert into my audit table.
    Last edited by jagadishgurram; 04-22-10 at 10:39.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's because you are using ELSE. Since the statement was satisfied by the first clause (and the first record was inserted), the second clause is never executed.

    And no, this is not a good way to write this trigger or implement audit trail functionality. You should do a little more research before heading further down this path, or you will wind up with something that will just embarass you when you look at it again a year or two from now.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2010
    Location
    Hyderabad
    Posts
    12

    Update Trigger for audit table

    Hi,

    I have a table (CMSAddress) which contains some n number of fields, i am updating my details but when i am updating i want to maintain and insert filed names like column name,oldvalue,new value into another table which fields are updated.

    I used trigger for updating but it is working fine
    My Query is

    CREATE TRIGGER TRIGGER_CMSAddress
    ON CMSAddress
    AFTER UPDATE
    AS
    IF ( UPDATE (AddressLine1) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'Address Line1',(select AddressLine1 from deleted),ins.AddressLine1,getdate()
    FROM inserted ins
    END
    IF ( UPDATE (AddressLine2) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'Address Line2',(select AddressLine2 from deleted),ins.AddressLine2,getdate()
    FROM inserted ins
    END
    IF ( UPDATE (CityName) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'CityNam e',(select CityName from deleted),ins.CityName,getdate()
    FROM inserted ins
    END
    IF ( UPDATE (StateName) )
    BEGIN
    INSERT INTO CMSHistory
    (ObjectId,ReferenceId,EntityId,ColumnName,OldValue ,NewValue,ModifiedDate)
    SELECT
    'CMSAddress',ins.ReferenceId,ins.EntityId,'StateNa me',(select StateName from deleted),ins.StateName,getdate()
    FROM inserted ins
    END

    GO



    But i dont want to check each column like "IF ( UPDATE (each column name)
    ) " i required the query without mentioning each column name means column name to be checked dynamically.

  7. #7
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Then you should rethink how you want to store audit data. You could capture everything. You could have your application write out the records as you want them stored. I suppose if you really wanted to do this dynamically, you could query the columns view and compare it against your old values to see if any of them changed, but that will be a complex operation.
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •