Triggers in Sql Server

Introduction:

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.

Definition:

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.

Example:

CREATE TRIGGER myTrigger ON Employee FOR INSERT, UPDATE AS

IF EXISTS (SELECT 'True' FROM Inserted i JOIN employee e

ON i.ID = e.ID)
BEGIN
RAISERROR ('Transaction Failed.', 16, 1)

ROLLBACK TRAN
END
GO

Types of Triggers

There are some added types in SQL Server 2005 for triggering actions:

1.    DML Triggers

AFTER Triggers

INSTEAD OF Triggers

2.    DDL Triggers

3.    CLR Triggers

DML 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.

    1. 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.

Example:

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

DECLARE

duplicate_info EXCEPTION;

PRAGMA EXCEPTION_INIT (duplicate_info, -00001);

BEGIN

INSERT INTO customers

(customer_id, cust_last_name, cust_first_name)

VALUES (

: new.customer_id,

: new.cust_last_name,

: new.cust_first_name);

INSERT INTO orders (order_id, order_date, customer_id)

VALUES (

: new.order_id,

: new.order_date,

: new.customer_id);

EXCEPTION

WHEN duplicate_info THEN

RAISE_APPLICATION_ERROR (

num=> -20107,

msg=> ‘Duplicate customer or order ID’);

END order_info_insert;

/

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);

    1. 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.

Example:

                       ------ Creating a DML trigger in T-SQL -------
SET NOCOUNT ON
CREATE TABLE UserTable (User_ID int IDENTITY, User_Name varchar (30), Type varchar (10))
go
CREATE TRIGGER tr_User_INSERT
ON UserTable
FOR INSERT
AS
PRINT GETDATE ()
Go
                                                             INSERT UserTable (User_Name, Type) VALUES ('James''ADMIN') 
------ Result ---------------
Apr 30 2007 7:04AM

DDL Triggers:

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.

CLR Triggers:

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

Advertisements

3 responses

  1. Very informative post. Its really helpful for me and beginner too. Check out this link too its also having a nice post related to this post over the internet which also explained very well…
    http://mindstick.com/Articles/f7074849-0e9e-463b-a12d-f3d7a35b2785/?Trigger%20in%20SQL%20Server

    Thanks Everyone!!

  2. Suppose I have a table in a database (TblExample) contained three columns (Name, Age, City). Now after sometimes someone added one extra column (country). Now, how I will get the newly added column of the Table i.e. country.

    Is there any way to track the changes of table structure, whenever anybody changes the structure of table.

    Thanks.

  3. I got this website from my friend who told me concerning this web site and at the moment this time I am visiting this web page and reading very informative content here. debdfaacbeae

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: