Post reply

create trigger on all tables for insert,update,delete

  • July 13, 2008 at 1:56 pm

    #122369

    Hi,

    can i create a trigger on all tables for insert,update,delete that will write to an audit table what is the values in the column before the update and after the update?

    if so how it can be done?

    THX

  • July 13, 2008 at 1:59 pm

    #842131

    of course u can, i have found some on the site u just search for auditing triggers.

    but u might need to adjust the scripts a littel bit.

    ..>>..

    MobashA

  • July 13, 2008 at 3:41 pm

    #842136

    are you sure you wanteed to audit all tables, that is quite an overhead to place on the database.

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 13, 2008 at 7:14 pm

    #842153

    Yes, you can, but you should really consider what needs to be audited and why. As has been pointed out, that's a lot of overhead. Also, you'll need to consider how much things are changing because of the additional space requirements of auditing everything as well as the potential blocking issues just for the audit itself. Also, the type of data types for the columns may make a difference on the types of triggers you use. For instance, text, ntext, and image columns cannot be properly audited using AFTER triggers (one of the reasons these data types are deprecated in SQL Server 2005). INSTEAD OF triggers are required, but then you must still be sure to write to the table within the trigger itself.

    K. Brian Kelley
    @kbriankelley

Viewing 4 posts - 1 through 3 (of 3 total)

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