[Trigger] Update a Datetime column when insert/update/delete

• evald

  SSCrazy

  Points: 2165

  More actions

  October 28, 2011 at 8:54 am

  #390714

  Hello,

  I have a table which has a column called DATA_STORICO of type smalldatetime.

  This table is used to historicize some data. All the data are pulled to this table using a SSIS package.

  My requirements are that every time an insert is done the DATA_STORICO column must contain the current time.

  So, i have tried to create a trigger that does this job but it does not work.

  The SSIS package execute and pull the data to the table but all rows contains null values on the DATA_STORICO column.

  The strange thing is that if i try to insert a row from SSMS it works!!!

  USE [MY_DB]

  ALTER DATABASE MY_DB

  SET RECURSIVE_TRIGGERS OFF

  GO

  IF OBJECT_ID ('SetDataStorico','TR') IS NOT NULL

  DROP TRIGGER SetDataStorico;

  GO

  CREATE TRIGGER SetDataStorico

  ON dbo.GiacenzeStorico

  AFTER INSERT, UPDATE

  AS

  UPDATE dbo.GiacenzeStorico SET DATA_STORICO = GETDATE() WHERE DATA_STORICO IS NULL

  GO

  Any idea?

  Thank you

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  October 28, 2011 at 9:12 am

  #1400673

  Check the SSIS package(s). IIRC they can actually be setup to not fire triggers on insert.

• evald

  SSCrazy

  Points: 2165

  More actions

  October 28, 2011 at 9:18 am

  #1400677

  You're Right!!!

  Thank you.:-)

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 28, 2011 at 9:20 am

  #1400678

  Bulk operations in general, regardless of where they originate, don't fire triggers by default.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass