CODEWITHSUNDEEP
sqlsql-server
N4t4ly4
N4t4ly4

Reputation: 41

INSERTED table not retrieving values inserted after truncating

I have a Trigger that makes changes to a columns value in a table if the value is updated/inserted.

If I do a direct UPDATE statement to the table the Trigger works. If I run a stored procedure to truncate the table and repopulate the table (data warehouse table/ETL)the trigger does not work.

I am not sure why the trigger is not working after I run the stored procedure. Any ideas? Thank you ahead of time.

HERE is the trigger:

CREATE TRIGGER TriggerName
    ON PlanesTable
    AFTER UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @vPlaneID INT;

    SELECT @vPlaneID = INSERTED.PlaneID
    FROM INSERTED;

    UPDATE dwPlanesDim
    SET ServiceStatus = CASE WHEN DATEDIFF(DAY, LastService,GETDATE())  <= 30 THEN 'Current' 
        ELSE 'Review Service'
        END
    WHERE @vPlaneID = PlaneID
    SET NOCOUNT OFF;
END;
GO

Upvotes: 0

Views: 315

Answers (2)

Udai Krishnan
Udai Krishnan

Reputation: 76

Not firing any trigger is an expected outcome using a TRUNCATE.

Extract: TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. https://learn.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql

Upvotes: 1

Xedni
Xedni

Reputation: 4695

TRUNCATE is not an update. In fact technically, it's not even DML. TRUNCATE works so quickly specifically because it doesn't log any individual rows. It just deallocates the disk space used by the table. Consequently, those inserted/deleted tables are not available after a truncation.

Additionally, if you truncate the table and then populate the table, that will be an insert, not an update. Since this trigger is set only to fire after UPDATE, it won't fire in that case.

Upvotes: 2

Related Questions