over 9 years ago
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
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.
0 Comment(s)