Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • SQL Server : Instead of Trigger

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 192
    Comment on it

    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.

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: