Delete trigger does not catch table truncation
Sample shows table truncation will not fire delete trigger.
USE AdventureWorks;
GO
-- STAGING
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'est_del_trigger_log' AND type = 'U')
DROP TABLE test_del_trigger_log;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = 'est_del_trigger' AND type = 'U')
DROP TABLE test_del_trigger;
GO
CREATE TABLE test_del_trigger
(id INT IDENTITY(1,1)
,tkt VARCHAR(10)
CONSTRAINT pk_test_del_trigger PRIMARY KEY (id)
);
GO
INSERT INTO test_del_trigger
SELECT 'la la l1' UNION ALL
SELECT 'la la l2' UNION ALL
SELECT 'la la l3' UNION ALL
SELECT 'la la l4';
GO 10
CREATE TABLE test_del_trigger_log
(id INT IDENTITY(1,1)
,deleteTime SMALLDATETIME DEFAULT(GETDATE())
,nof_rows INT
CONSTRAINT pk_test_del_trigger_log PRIMARY KEY (id)
);
GO
CREATE TRIGGER test_del_trigger_Dtrig ON test_del_trigger
FOR DELETE
AS
-- SIMPLE LOG
INSERT INTO test_del_trigger_log (nof_rows)
SELECT @@rowcount AS nof_rows
GO
-- INITIAL DATA
SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO
-- DELETE 1 ROW;
-- DATA IS LOGGED IN LOG TABLE
DELETE FROM test_del_trigger
WHERE id = 1;
SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO
-- DELETE 11 (and higher) ROWS;
-- DATA IS LOGGED IN LOG TABLE
DELETE FROM test_del_trigger
WHERE id > 10
SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO
-- TABLE TRUNCATION
-- NO LOG
TRUNCATE TABLE test_del_trigger
SELECT * FROM test_del_trigger
SELECT * FROM test_del_trigger_log
GO