Call now: (800) 766-1884  



 Home


 SQL Server Tips
 SQL Server Training

 SQL Server Consulting
 SQL Server Support
 SQL Server Remote DBA



 Articles
 Services
 SQL Server Scripts
 Scripts Menu



 

 

 

   
  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

AS

SET NOCOUNT ON

/* * Prevent INSERT if not within the first 5 days of the month */

IF (day(getdate())>5)

        RAISERROR 44447 'The record can''t be added. Insertion is restricted to first 5 days of the week.'

ELSE

      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 */

(day(getdate())>5))

 

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 trigger.

* 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:

Super SQL Server Systems
Turbocharge Database Performance with C++ External Procedures

ISBN: 0-9761573-2-2
Joseph Gama, P. J. Naughter

 http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm
 

 

Burleson Consulting Remote DB Administration


 

 


 

 

 

 

 
Burleson is the America's Team

Note: The pages on this site were created as a support and training reference for use by our staff of DBA consultants.  If you find it confusing, please exit this page.

Errata?  SQL Server technology is changing and we strive to update our SQL Server support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:and include the URL for the page.
 


Burleson Consulting
SQL Server database support

 

Copyright © 1996 -  2013 by Vaaltech Web Services. All rights reserved.

Hit Counter