A trigger is a mechanism that is invoked when a particular action occurs on a particular table. Each trigger has three general parts: A name The action The execution The maximum size of a trigger name is 128 characters. The action of a trigger can be either a DML statement (INSERT, UPDATE, or DELETE) or… [Continue Reading]
This chapter is dedicated to a mechanism called a trigger. The beginning of the chapter describes Transact-SQL statements for creating, deleting, and modifying triggers. After that, examples of different application areas for DML triggers are given. Each example is created using one of three statements, INSERT, UPDATE, or DELETE. The second part of the chapter covers DDL triggers, which are based on DDL statements such as CREATE TABLE. Again, examples of different application areas related to DDL triggers are given. The end of the chapter discusses the implementation of triggers using CLR (Common Language Runtime).
As you already know, AFTER triggers fire after the triggering action has been processed. You can specify an AFTER trigger by using either the AFTER or FOR reserved keyword. AFTER triggers can be created only on base tables. AFTER triggers can be used to perform the following actions, among others: Create an audit trail of… [Continue Reading]
A trigger with the INSTEAD OF clause replaces the corresponding triggering action. It is executed after the corresponding inserted and deleted tables are created, but before any integrity constraint or any other action is performed. INSTEAD OF triggers can be created on tables as well as on views. When a Transact-SQL statement references a view… [Continue Reading]
The Database Engine allows multiple triggers to be created for each table or view and for each modification action (INSERT, UPDATE, and DELETE) on them. Additionally, you can specify the order of multiple triggers defined for a given action. Using the system stored procedure sp_settriggerorder, you can specify that one of the AFTER triggers associated… [Continue Reading]
The first part of this chapter described DML triggers, which specify an action that is performed by the server when a modification of the table using an INSERT, UPDATE, or DELETE statement is executed. The Database Engine allows you to define triggers for DDL statements, such as CREATE DATABASE, DROP TABLE, and ALTER TABLE. The… [Continue Reading]
Triggers, as well as stored procedures and user-defined functions, can be implemented using the Common Language Runtime (CLR). The following steps are necessary if you want to implement, compile, and store CLR triggers: Implement a trigger using C# or Visual Basic and compile the program using the corresponding compiler (see Examples 14.9 and 14.10). Use… [Continue Reading]