Instead of trigger helps to resolve interesting problems like this one:

I have a dummy table where I post transactions. I do an insert into them and I let the trigger consume transactions. Inserted special table exists writing of SQL set-oriented operations with my transactions. I never really do the real insert into the dummy transaction table, so I doesn't have to delete inserted the rows. I just process special Inserted table's data. Why not write a stored procedure to do just that ? The dummy table is a mean by which I pass a table to a T-SQL code like a stored procedure. Deleted and inserted table are since SQL2005 read-only temp tables located in tempdb. Probably there is less overhead writing to these tables because there are read-only so no rollback is necessary on them.

Instead of trigger help manage situations where you dump data to a table with unique index, and you takes cares yourself of the duplicate. Duplicate doesn't occurs on the table before you reach trigger code, because data is not yet into the table and will never be. You can clean-up inserted data and handle it.

The same holds true for instead of trigger over views. You can make views to use them as virtual grid interface for front end applications, or other programs. The view shows processed data that helps to make user of program to make a decision, but you implement business rules of what you keep from the update to the views. You can use this views to generate a denormalized views that mimics a an unormalized table, normalize table, drop the unnormalized ones and take the view to generated the old unnormalized view of the table under the original table name. Existing code will continue to work with the denormalized stuff, and you can start new code using normalize data.

Maurice3/12/2009 10:15:35 AM

How do we create triggers in SQL Server 2005?

Anonymous User 8/23/2005 2:40:36 PM

Your Comments (required): This article provided me a pretty good understanding of INSTEAD OF and AFTER Triggers and their use.
My question is where can I find the next article of the author. Please respond to this affect to pankaja_us@yahoo.com

Thanks in advance.

Name (required):PANKAJA 6/12/2004 8:57:24 PM

i like to write trigger on Old and New values can you help me out?

Gaffar Mohammad 6/11/2004 5:14:45 AM

1. what are the different type of instead of triggers with example?
2. what are the different types of indexes with example?

rajasekaran_ve@yahoo.com 5/18/2004 4:20:35 AM

I read the article and I have a question: I have a self reference (one of table columns is foreigh key to the other one) database table. I created a INSTEAD OF DELETE trigger to recursive delete the table records. I set the recursive triggers database option to on. But I still cann't delete table records, the foreign key violation occurs?
Thank you.

Nikolai3/31/2004 3:17:15 AM