What are "Instead of" Triggers?

Question

In SQL Server, what are Instead Of triggers?

Answer 1

From Database Journal:

What are "Instead Of" triggers?

In previous versions of SQL Server, Triggers were sections of code that were attached to tables and executed automatically after pre-defined updates took place on a specified table. These of course still exist in SQL Server 2000 and above. Instead Of Triggers are attached to a table in a similar way, but the code inside them is executed in place of the original updating statement.

Answer 2

Instead of triggers are actually very cool. In the past I've used instead of triggers on a 'read-only' view to make it behave like a table.

This is actually very cool.

You can even write these triggers in .NET if T-SQL isn't one of your strengths.

Answer 3

Triggers are basically used to enforce Business constraints. The Business constraints can be implemented in the following order: Entity Integrity(Primary/Unique Key), Domain Integrity(Check constraint),Referential Integrity(Foreign Key) and finally triggers.Triggers are used to enforce business constraints, which are complex.

Triggers are like stored procedures, which are automatically get executed based on Data Manipulation action. "Instead of Triggers" are used to handle data manipulations, which we want to handle ourselves. Intead of INSERT,UPDATE, DELETE are written on a table to study the data and take the necessary action ourselves.

Eg. If you don't want DELETE to happen on table and want to monitor users, who are trying to delete data in table. We can write INSTEAD OF TRIGGER and write the currentuser information to Audit table.

Eg. If you want to throw custom messages for an user action. In an Exam timetable, if a user tries to have exam on weekend,we can throw a custom message that "It is Weekend.Take Rest"

Eg.If we have a view is defined on multiple tables and an insert is tried on it, we can have instead of trigger to carryout the corresponding insert in the base tables.

Warm Regards, Venkat