||SQL Server Tips by Gama and Naughter
INSTEAD OF triggers
This type of trigger will take the place of the operation that set
it off. The operation that caused the trigger to fire will be
ignored and the code from the trigger will execute.
CREATE TRIGGER "jobs_ITrig" ON jobs
INSTEAD OF INSERT
SET NOCOUNT ON
/* * Prevent INSERT if not within the first 5 days of the month */
RAISERROR 44447 'The record can''t be added. Insertion is
restricted to first 5 days of the week.'
INSERT jobs(job_desc, min_lvl, max_lvl)
SELECT job_desc, min_lvl, max_lvl FROM INSERTED
This trigger will do exactly the same as the other one. The AFTER
trigger needs to rollback the transaction to revert the insertion
because the inserted row is already in the jobs table but the
transaction is not committed yet. The INSTEAD OF trigger doesn’t
need to rollback any transaction because none occurred. If the data
needs to be inserted it will have to do the insertion by getting the
data from the virtual table INSERTED.
Why not use a check? Like this:
ALTER TABLE dbo.jobs ADD CONSTRAINT
CK_jobs_5days CHECK (/* * Prevent INSERT/UPDATE if not within
the first 5 days of the month */
This would work fine and it would be a better choice but these two
examples should be simple and focus on the differences between the
two types of triggers.
Tables and updatable views can reference several triggers bound,
although views can only reference INSTEAD OF triggers. Views WITH
CHECK OPTION cannot reference INSTEAD OF triggers. The WITH CHECK
OPTION must be removed with ALTER TABLE before creating the trigger.
Tables and views can have many AFTER triggers but only one INSTEAD
OF trigger for each INSERT, UPDATE, or DELETE statements. When it is
necessary to use more than one INSTEAD OF trigger in a table, this
is possible by using views with triggers.
When should we use an AFTER trigger vs. INSTEAD OF trigger?
* If the data modification will always happen - AFTER trigger.
* If the data modification will never happen - INSTEAD OF
* If the data modification will happen more often than the
alternate code - AFTER trigger.
* If the data modification will happen less often than the
alternate code - INSTEAD OF trigger.
Triggers are used for:
* Enforcing referential integrity.
* Enforcing business rules.
* Maintenance/Administrative purposes.
* Security purposes.
The above book excerpt is from:
Turbocharge Database Performance with C++ External Procedures
Joseph Gama, P. J. Naughter