Instead of Trigger

Instead of trigger is used to fire instead of event such as INSERT, DELETE AND UPDATE. It is commonly used to correctly update the view.

I have already a table, Your can make your own.

Select * from Hackerfriend

select * from userdetails

Let’s create a view first to understand this concept clearly.

CREATE VIEW vwHackerfriend

AS

SELECT h.username, u.nickname

FROM Hackerfriend h

INNER JOIN userdetails u

ON h.userid = u.userid

I used INNER JOIN to make a view.

View is look like this.

If you want to delete any records from view table you cannot delete it and shown below error. This because of this modification affects multiple tables.

delete from vwHackerfriend where username = 'Sameer kaushik'

To overcome this problem We will use Instead of trigger.

CREATE TRIGGER tr_vwHackerfriend_InsteadOfDelete

ON vwHackerfriend

INSTEAD OF DELETE

AS

BEGIN

  -- Using Inner Join

  DELETE FROM Hackerfriend

  FROM Hackerfriend h

  INNER JOIN DELETED del

  ON h.username = del.username 

END

You can view vwHackerfriend and trigger in the same database.

Now execute delete command.

delete from vwHackerfriend where username = 'Sameer kaushik'

You will surprised from message. Right?

This is due it was deleted the record from multiple table.

select * from vwHackerfriend

Select * from Hackerfriend

I hope this article is useful for you. If you find any mistake or suggestion please comment.

Happy Coding!!

Rahul Kumar

Friend of Hackersfriend.