center4code: Trigger in SQLSERVER

Trigger

Triggers are special types of Stored Procedures that are defined to execute automatically in place of or after data modifications. They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions.

There are two different types of triggers in Microsoft SQL Server 2000. They are INSTEAD OF triggers and AFTER triggers. These triggers differ from each other in terms of their purpose and when they are fired. In this article we shall discuss each type of trigger.

But in Sql server 2005 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. The trigger and the statement that fires it are treated as a single transaction. Using this we can cascade changes in related tables, can do check operations for satisfying some rules and can get noticed through firing Mails. We can even execute multiple triggering actions by creating multiple Triggers of same action type on a table. We have to specify the modification action(s) at the Table level that fires the trigger when it is created.

AFTER Triggers

As the name specifies, 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. AFTER triggers can be specified on tables only. Here is a sample trigger creation statement on the Users table.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables, where they can extend the types of updates a view can support.

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

Before delving into DDL triggers, learning what DDL triggers have in common with DML triggers may be helpful. First, DDL triggers are created, changed, and removed by using statements such as CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER. The following is a typical DDL CREATE TRIGGER statement:

CREATE TRIGGER ddl_trig_loginAW

ON ALL SERVER

FOR DDL_LOGIN_EVENTS

AS

   PRINT 'Added trigger event to DDLServerTriggerData'

   INSERT INTO [AdventureWorks].[dbo].[dbo.DDLServerTriggerData]

               (DDLServerEvent) VALUES ( EVENTDATA())

Also, like DML triggers, DDL triggers run in the same transaction as the DML statement. So for instance, when a DDL TSQL statement has completed, you can rollback a transaction whenever appropriate.

Finally, both DDL and DML triggers can use the new CLR functionality in SQL Server 2005 for running a managed code assembly uploaded to SQL Server.

Unlike DML triggers, DDL triggers respond to completely different events. As previously stated, DDL triggers respond to changes to a database or a server. Each time DDL statements make changes using commands such as CREATE TABLE, an event is fired. DDL triggers can subscribe to those events and execute other TSQL instructions in response to the events.

Some other differences between DDL and DML triggers include the following:

DDL triggers do not support the INSTEAD of functionality in the CREATE TRIGGER statement.
DDL triggers are missing the inserted and deleted tables common to DML triggers.

Now that you're familiar with what DDL triggers do, you can set up a DDL trigger of your own.

Designing a DDL Trigger

When you define a DDL trigger, you must decide on the scope of your trigger. Scope determines whether the trigger executes at the database or the server level. (A forthcoming section discusses scope in more detail later in the article.)

After determining scope, you must decide whether the DDL trigger responds to a single DDL statement or a group of related statements. For example, the DDL trigger can respond to the DROP TABLE statement or all table operations (DROP, CREATE, ALTER). Typically, single events are named by using the TSQL statement and separating each word in the statement with an underscore (for example, CREATE_DATABASE). (Refer to the SQL Server 2005 Books Online for a complete list of events a DDL trigger can subscribe to.)

Once you've determined scope and events, you are ready to code the proper response. You will find the EVENTDATA() TSQL function most helpful for coding your response.

The EVENTDATA() Function

The EVENTDATA() function returns XML data with information such as event time, System Process ID (SPID), and type of event firing the trigger. A DDL trigger uses the EVENTDATA() function to determine how it must respond.

To retrieve information from XML data, you must use XQuery. A complete discussion of XQuery and the new XML Datatype is beyond the scope of this article. You can find an introduction of the XML Datatype on Developer.com at "The Fundamentals of the SQL Server 2005 XML 3Datatype".

The following is a sample of XML data returned by the EVENTDATA() function:

<EVENT_INSTANCE>

   <EventType>CREATE_TABLE</EventType>

   <PostTime>2005-07-30T10:48:52.537</PostTime>

   <SPID>55</SPID>

   <ServerName>PSE-TEST-JON1</ServerName>

   <LoginName>PSE-TEST-JON1\Administrator</LoginName>

   <UserName>dbo</UserName>

   <DatabaseName>AdventureWorks</DatabaseName>

   <SchemaName>Person</SchemaName>

   <ObjectName>Address</ObjectName>

   <ObjectType>TABLE</ObjectType>

   <TSQLCommand>

      <SetOptions ANSI_NULLS="ON"

                  ANSI_NULL_DEFAULT="ON"

                  ANSI_PADDING="ON"

                  QUOTED_IDENTIFIER="ON"

                  ENCRYPTED="FALSE" />

      <CommandText>CREATE TABLE [Person].[Address](

                                [AddressID] [int]

                                IDENTITY (1, 1)

                                NOT FOR REPLICATION NOT NULL,

      [AddressLine1] [nvarchar](60) NOT NULL,

      [AddressLine2] [nvarchar](60) NULL,

      [City] [nvarchar](30) NOT NULL,

      [StateProvinceID] [int] NOT NULL,

      [PostalCode] [nvarchar](15) NOT NULL,

      [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL CONSTRAINT

         [DF_Address_rowguid] DEFAULT (NEWID()),

      [ModifiedDate] [datetime] NOT NULL CONSTRAINT

         [DF_Address_ModifiedDate] DEFAULT (GETDATE())

      ) ON [PRIMARY];</CommandText>

   </TSQLCommand>

</EVENT_INSTANCE>

As you can see, there are other pieces of information that depend on the DDL statement firing the trigger. (See the SQL Server 2005 Books Online for a complete list of XML Schemas.) The following sample code illustrates how you will access the XML returned by the EVENTDATA() function:

DECLARE @data XML;

DECLARE @schema sysname;

DECLARE @object sysname;

DECLARE @eventType sysname;

SET @data = EVENTDATA();

SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]',

                             'sysname');

SET @schema    = @data.value('(/EVENT_INSTANCE/SchemaName)[1]',

                             'sysname');

SET @object    = @data.value('(/EVENT_INSTANCE/ObjectName)[1]',

                             'sysname')

One important point concerning XQuery: If you're going to retrieve single values from the EVENTDATA() XML, use the "value" statement rather than the "query" statement. You will have better results with the formatting of the XML data.

DDL triggers can fire for changes in either the database or server

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 Trigger

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

Monday, December 17, 2007

Trigger in SQLSERVER

Introducing DDL Triggers

Designing a DDL Trigger

The EVENTDATA() Function