|
|
Triggers are stored procedures which are fired when data is modified in an underlying table. They can evaluate data being added to a table for validation purposes, or can make changes in that or other fields depending on the value of that data. You can use them even to execute a separate stored procedure, or to roll back a data modification or an entire transaction.
In earlier versions of SQL Server, triggers were used to maintain referential integrity. In current versions, constraints and foreign keys are used to accomplish much of those tasks, but triggers are still used to accomplish more complex tasks than that are available to the built in newer tools, such as complex column constraints, evaluation of tables in other databases, complicated defaults, or cascading routines involving multiple changes in multiple tables.
Triggers are created in the Enterprise Manager, or in the Query Analyzer through the object browser. There are also templates for triggers in the Query Analyzer (Edit|Insert Trigger). Triggers can be created with the following syntax:
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ... n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ... n ]
} ]
sql_statement [ ... n ]
}
}
There are two types of triggers: AFTER and INSTEAD OF. After triggers AFTER TRIGGERS fire after the data is changed, either by insert, delete, or update. If the data is inappropriate, as defined in the trigger, the modification can be rolled back to where it was before the data was modified. After triggers AFTER TRIGGERS cannot be placed on views, and cannot be used on more than one table. Also, the text, ntext, and image columns cannot be referenced in an after trigger. AFTER TRIGGERS.
After triggers AFTER TRIGGERS can be nested to 32 levels deep, and can be called recursively, again to 32 levels.
Instead of INSTEAD OF triggers make the validation before the modification. However, Instead of INSTEAD OF triggers CAN can be used on views. They do not allow recursion, and you can only have one Instead of INSTEAD OF trigger per table. And you cannot use an Instead of INSTEAD OF trigger with a cascade.
Resources
? Information on Constraints and Triggers
This resource discusses about constraints and triggers in detail.
? Information: Create Trigger
This resource is useful in understanding the workings of triggers in detail.
Metro NY / NJ SQL Server Consultants
We specialize is custom database software. Call us for a free consultation (973) 635 0080 or email us at paladn.com |
| Author: Chris Kemp |
| |
Author Bio:
|
|
 |
|
| |
Related Articles |
| |
Types of Computer Software
|
| |
Avangate Sponsors European Shareware Conference (eswc)
|
| |
Alternatives to Hybrid cars
|
| |
Step By Step: Add Image Validation To Your Website Form
|
| |
How To Get Cheap Term Insurance
|
| |
Microsoft Navision Implementation, Integration & Customization ?C S?o Paulo ERP market notes
|
| |
VOIP software guide 101
|
| |
Audi A8: Mercedes, BMW Beware!
|
| |
Industrial Fasteners Hold It All Together
|
| |
Subaru Legacy Chases Impreza's Footsteps
|
| |
|
|
|
|
| |
|
It??s not very difficult to find car insurance online in Canada, but locating those that offer the m ... - James M Taylor |
| |
|
MPEG, or Moving Picture Experts Group, is a jointly working group comprised of the International Sta ... - mansi gupta |
| |
|
The Jaguar is a cat but it is surely no kitten. It is a fierce one, not wanting to be left behind an ... - Michelle Crimson |
| |
|
|
| |
|
The first question that needs to be answered is ? Is it even possible to get cheap car insurance? Th ... - Jill Quinn |
| |
|
There are a number of services that run in the background as windows operating systems powers the PC ... - Arvind |
| |
|
Dynamic Packaging enables travelers to create and book their dream vacation and has captured the onl ... - Johnny Mayer |
| |
|
Some car crashes cannot be avoided and serious injury or death can follow. Please read what Mercedes ... - Matthew Keegan |
| |
|
Car-Share.com Tips To Improve Your Budget & The Environment! If you are having difficulty balanc ... - D. Carter |
| |
|
| |
|
|
