Post reply

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

  • 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

  • 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.

  • October 28, 2011 at 9:18 am

    #1400677

    You're Right!!!

    Thank you.:-)

  • 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

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

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