Categories
Sql Server

DELETE Trigger in SQL Server

DELETE Trigger in SQL Server

A DELETE trigger is a kind of stored procedure that executes itself when a DELETE statement deletes data from a table or view on which the trigger has been configured.

When a DELETE trigger is fired, deleted rows from the affected table are placed in a special table i.e (deleted table). Deleted table:-A Logical table that holds a copy of the rows that have been deleted.

Some facts about Delete Trigger:-

  1. When a row is appended to the deleted table, it no longer exists in the database table.
  2. Space is allocated from memory to create the deleted table. The deleted table is always in the cache.
  3. A trigger that is defined for a DELETE action does not execute itself whenever a TRUNCATE statement has been used because TRUNCATE TABLE is not logged and the delete trigger does not fire.

How to create a Delete Trigger:-

--------For Not Allowing Deletion----------
create trigger del_records on flatfile
for delete
as
begin
rollback
print'Delete is not allowed Contact your System administrator'
end


-----Allow Delete But Logged Delete Data into reference table----

Create TRIGGER Del_logged 
ON flatfile 
AFTER DELETE 
AS 
BEGIN 
INSERT INTO flatfile_logged SELECT * FROM DELETED 
END

After Creating First trigger, delete command will Rollback due to the trigger.In second trigger delete command will work, also deleted record will be logged into a separate table.