To Populate Insert, Update Audit Columns

• me_balaji

  SSC Eights!

  Points: 923

  More actions

  March 13, 2006 at 10:23 am

  #152824

  I need to know is there a way I can override the user values and use the system values all the time.

  On insert

  - Force the values of INSERT_DTTM and INSERT_UTC_OFFSET to be current system time and current user's UTC offset, respectively, Also, force the values of UPDATE_DTTM and UPDATE_UTC_OFFSET to be null.

  On update

  - Force the values of INSERT_DTTM and INSERT_UTC_OFFSET to remain unchanged from before the update. Force the values of UPDATE_DTTM and UPDATE_UTC_OFFSET to be current system time and current user's UTC offset.

  In all cases, I want to overwrite whatever the client program attempted to put into the audit columns, so a column-level DEFAULT value is inadequate

   

  I cannot use AFTER Tiggers because nested triggers is set on in the server level which I cannot change due functional reasons.

  Can I use computed columns instead.

  What will be the drawback on it.

  Please do let me know.

   

  Thanks

• Jo Pattyn

  SSC-Dedicated

  Points: 32386

  More actions

  March 13, 2006 at 11:14 am

  #626319

  You could use instead of triggers.

  from the books online

  INSTEAD OF INSERT Triggers

  INSTEAD OF INSERT triggers can be defined on a view or table to replace the standard action of the INSERT statement. Usually, the INSTEAD OF INSERT trigger is defined on a view to insert data into one or more base tables.

  Columns in the view select list can be nullable or not nullable. If a view column does not allow nulls, an INSERT statement must provide values for the column. View columns allow nulls if the expression defining the view column includes items such as: