The SQL CREATE TRIGGER statement provides a way for the database management system to actively control, monitor, and manage a group of tables whenever an insert, update, or delete operation is performed. The statements specified in the SQL trigger are executed each time an SQL insert, update, or delete operation is performed. An SQL trigger may call stored procedures or user-defined functions to perform additional processing when the trigger is executed.
Unlike stored procedures, an SQL trigger cannot be directly called from an application. Instead, an SQL trigger is invoked by the database management system on the execution of a triggering insert, update, or delete operation. The definition of the SQL trigger is stored in the database management system and is invoked by the database management system, when the SQL table, that the trigger is defined on, is modified.
A trigger is a Database object just like a stored procedure or we can say it is a special kind of Stored procedure which fires after (/before) a specified language event executes. More specifically, it is for the object which is attached to a Table or View or Database schemas for tracking the operations on them. The main difference between a trigger and a stored procedure is that the former is attached to a table or view and is fired only when an INSERT, UPDATE, and/or DELETE occurs, while a stored procedure executes at any time when it is called.
TRIGGER myTrigger ON Employee FOR INSERT,
IF EXISTS (
Inserted i JOIN employee e
ON i.ID = e.ID)
Types of Triggers
There are some added types in SQL Server 2005 for triggering actions:
1. DML Triggers
INSTEAD OF Triggers
2. DDL Triggers
3. CLR Triggers
These triggers are fired when a Data Manipulation Language (DML) event takes place. These are attached to a Table or View and are fired only when an INSERT, UPDATE and/or DELETE event occurs.
- INSTEAD OF Triggers:
INSTEAD-OF triggers are objects that will execute instead of data manipulation statements in the database engine. For example, attaching an INSTEAD-OF INSERT trigger to a table will tell the database engine to execute that trigger instead of executing the statement that would insert values into that table.
Why INSTEAD OF Trigger?
INSTEAD-OF triggers are very powerful objects in SQL Server. They allow the developer to divert the database engine to do something different than what the user is trying to do. An example of this would be to add an INSTEAD-OF trigger to any table in your database that rolls back transactions on tables that you do not want modified. You must be careful when using this method because the INSTEAD-OF trigger will need to be disabled before any specified modifications can occur to this table.
Perhaps a more functional reason to use an INSTEAD-OF trigger would be to add the trigger to a view. Adding an INSTEAD-OF trigger to a view essentially allows you to create updateable views. Updateable views allow you to totally abstract your database schema so you can potentially design a system in such a way that your database developers do not have to worry about the OLTP database schema and instead rely upon a standard set of views for data modifications.
CREATE VIEW order_info AS
SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
o.order_id, o.order_date, o.order_status
FROM customers c, orders o
WHERE c.customer_id = o.customer_id;
Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view. The PL/SQL trigger implementation is shown in italics.
CREATE OR REPLACE TRIGGER order_info_insert
INSTEAD OF INSERT ON order_info
PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
INSERT INTO customers
(customer_id, cust_last_name, cust_first_name)
INSERT INTO orders (order_id, order_date, customer_id)
WHEN duplicate_info THEN
msg=> ‘Duplicate customer or order ID’);
You can now insert into both base tables through the view (as long as all NOT NULL columns receive values):
INSERT INTO order_info VALUES
(999, ‘Smith’, ‘John’, 2500, ’13-MAR-2001′, 0);
- AFTER Triggers:
AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed. This is the only option available in earlier versions on Microsoft SQL Server and also AFTER triggers can be specified only on tables only. Here is a sample trigger creation statement on the Users table.
------ Creating a DML trigger in T-SQL -------
SET NOCOUNT ON
CREATE TABLE UserTable (User_ID int IDENTITY, User_Name varchar (30), Type varchar (10))
CREATE TRIGGER tr_User_INSERT
PRINT GETDATE ()
Go INSERT UserTable (User_Name, Type) VALUES ('James', 'ADMIN')
------ Result ---------------
Apr 30 2007 7:04AM
DDL triggers are new to SQL Server 2005. This type of triggers, like regular triggers, fire stored procedures in response to an event. They fire in response to a variety of Data Definition Language (DDL) events. These events are specified by the T-SQL statements that are start with the keywords CREATE, ALTER, and DROP. Certain stored procedures that perform DDL-like operations can also fire this. These are used for administrative tasks like auditing and regulating database operations.
A CLR triggers can be any of the above, e.g. can be a DDL or DML one or can also be an AFTER or INSTEAD OF trigger. Here we need to execute one or more methods written in managed codes that are members of an assembly created in the ..Net framework. Again, that assembly must be deployed in SQL Server 2005 using CREATE assembly statement.
The Microsoft.SqlServer.Server Namespace contains the required classes and enumerations for this objective.
Steps for creating CLR Triggers:
The following are required steps for creating a CLR-Trigger of DML (After trigger) type for Insert action. This database Object is executed as the result of a user action against a table i.e. for an INSERT statement.
Creating a .NET class of triggering action
Making assembly(.DLL) from that Class
Enabling CLR environment in that database.
Registering the assembly in SQL Server
Creating CLR Trigger using that assembly