Triggers are saved processes which might be fired when info is modified within an underlying desk. They will Assess info getting added into a desk for validation purposes, or may make improvements in that or other fields based on the worth of that details. You may use them even to execute a different stored treatment, or to roll back a knowledge modification or a complete transaction.
In before versions of SQL Server, triggers had been utilized to take care of referential integrity. In existing versions, constraints and foreign keys are used to accomplish Considerably of People duties, but triggers remain used to accomplish more intricate tasks than that are offered towards the in-built newer instruments, like intricate column constraints, evaluation of tables in other databases, difficult defaults, or cascading routines involving many variations in multiple tables.
Triggers are developed from the Enterprise Supervisor, or while in the Question Analyzer through the object browser. You can also find templates for triggers within the Question Analyzer (EditInsert Trigger). Triggers might be produced with the next syntax:
Produce TRIGGER trigger_name
ON desk watch
[ WITH ENCRYPTION ]
FOR AFTER In lieu 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's two varieties of triggers: AFTER and INSTEAD OF. Following triggers Just after TRIGGERS fire once the info is altered, either by insert, delete, or update. If the data is inappropriate, as outlined inside the cause, the modification might be rolled again to the place it had been before the data was modified. Right after triggers Just after TRIGGERS can not be put on views, and cannot be employed on more than one desk. Also, the text, ntext, and graphic columns cannot be referenced in an following cause. Just after TRIGGERS.
Right after triggers Just after TRIGGERS might be nested to 32 ranges deep, and can be referred to as recursively, once again to 32 ranges.
Instead of As opposed to triggers make the validation ahead of the modification. Nonetheless, In place of INSTEAD OF triggers CAN can be used on views. They do not enable recursion, and you may only have a single Instead of In place of cause for every desk. And You can not use an Instead of INSTEAD OF result in having a cascade.
Resources
Info on Constraints and Triggers
This source discusses about constraints and triggers in detail.
Data: Build Trigger
This resource is useful in knowing the workings of triggers in detail.
Metro NY / NJ SQL Server Consultants
We focus is tailor made databases software.