A trigger is a special kind of a store procedure that executes in response to specific actions on the table like insertion, updation or deletion. It is a database object which is bound to a table. Triggers cannot be explicitly invoked. The only way to invoke them is by performing the required action on the table that they are assigned to.If there is an INSTEAD OF Trigger attached to a table than it is executed instead of the action query that causes it to fire.For example, if you define an Instead Of trigger on a table for the Delete operation, and than try to delete rows, they will not actually get deleted.

INSTEAD OF TRIGGERS can be classified as below:

1) INSTEAD OF INSERT Trigger.

2) INSTEAD OF UPDATE Trigger.

3) INSTEAD OF DELETE Trigger.

Lets create an Instead Of Delete Trigger:

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[FileList] 
INSTEAD OF DELETE
AS
    declare @fileId int;
    declare @fileName varchar(100);
    declare @fileSize int;  
    select @fileId=d.FileID from deleted d;
    select @fileName=d.FileName from deleted d;
    select @fileSize=d.FileSize from deleted d;

    BEGIN
        if(@fileSize>10)
        begin
            RAISERROR('Cannot delete file if size exceeds' > 10,16,1);
            ROLLBACK;
        end
        else
        begin
            delete from FileList where FileID=@fileId;
            COMMIT;
            insert into FileListAudit(FileID,FileName,FileSize,Action,Timestamp)
            values(@fileId,@fileName,@fileSize,'Instead Of Delete Trigger',getdate());
            PRINT 'Record Deleted '
        end
    END
GO

This trigger will prevent the deletion of records from the table where fileSize > 10. If such a record is deleted,the transaction will be rolled back by the Instead Of Trigger, otherwise the

transaction will be committed.