Permanently deletion in case of instead of delete trigger

Permanently deletion is possible also in case of 'instead of delete' trigger on the table:

Delete statement in the 'instead of delete' trigger performs permanently deletion:

• The original deletion statement will not be executed - the code in the trigger will be executed instead.
• Deletion statement in the trigger will be execute as regular DELETE without the trigger code (and will not start infinite loop).

The common use of deletion from 'instead of delete' trigger is to write a row in LOG table before the deletion.

For example:

CREATE TABLE [dbo].[DelTests]

(

DelId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

DelStr nvarchar(50) NULL

)

GO

CREATE TRIGGER  [dbo].[trg_DelTests_delete] ON [dbo].[DelTests]

   instead of delete

AS 

BEGIN

DECLARE @Str nvarchar(50) = 'row that inserted from trg_DelTests_delete instead of'

INSERT INTO DelTests

select @Str from deleted

DELETE  DelTests WHERE DelId IN (select DelId from deleted)

END

GO

INSERT INTO DelTests (DelStr) VALUES ('DelStr1')

INSERT INTO DelTests (DelStr) VALUES ('DelStr2')

INSERT INTO DelTests (DelStr) VALUES ('DelStr2_1')

GO

select * from DelTests

GO

DELETE FROM DelTests WHERE DelStr = 'DelStr1' -- 1 row

GO

select * from DelTests

GO

/*

-- results:

DelStr2

DelStr2_1

row that inserted from trg_DelTests_delete instead

*/

DELETE FROM DelTests WHERE DelStr Like '%DelStr2%' -- 2 rows

GO

select * from DelTests

GO

/*

-- results:

row that inserted from trg_DelTests_delete instead

row that inserted from trg_DelTests_delete instead

row that inserted from trg_DelTests_delete instead

*/