Create SQL Trigger

June 8, 2008

General

A trigger is a stored procedure that automatically executes when an event occurs in the database server.Triggers execute when users try to modify something or behind an event that generate changes on the data base.There are three kind of triggers: for insert , update , and delete.The most important events correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like(data definition language) operations.Triggers can be created in the SQL Server 2005 Database Engine directly from Transact-SQL statements or from methods of assemblies that are created in the Microsoft .NET Framework common language runtime (CLR) and uploaded to an instance of SQL Server.Using SQL Server you can create lots of triggers for any specific statement.

Syntax:

CREATE TRIGGER trigger_nameON { ALL SERVER | DATABASE }

[ WITH <ddl_trigger_option> [ ,...n ] ]

{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME

< method specifier >  [ ; ] }<ddl_trigger_option> ::=

[ ENCRYPTION ]

[ EXECUTE AS Clause ]

<method_specifier> ::=

assembly_name.class_name.method_name

schema_name

Is the name of the schema to which a DML trigger belongs. DML triggers are scoped to the schema of the table or view on which they are created. schema_name cannot be specified for DDL or logon triggers.

trigger_name

Trigger name is the name of the trigger. Is not allowed to start the trigger name with # or ##.

table/view

Table or view are the table or view where trigger is executing on.Triggers cannot be defined on local or global temporary tables.

DATABASE

Current data base where changes are making on.

ALL SERVER

Is the current server where are the tables or views that we make changes.If specified, the trigger fires whenever event_type or event_group occurs anywhere in the current server.

WITH ENCRYPTION

Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.

EXECUTE AS

Specifies the way is executed the trigger.This way users validate permissions on any database objects that are referenced by the trigger.

FOR | AFTER

After - means that trigger can be executed only after all operations specified in the triggering SQL statement have executed successfully.

event_type

Is the type of event that release the trigger for executing.

sql_statement

Is the trigger conditions and actions. Trigger conditions specify additional criteria that determine whether the tried DML, DDL, or logon events cause the trigger actions to be performed.The trigger actions specified in the Transact-SQL statements go into effect when the operation is tried.

method_specifier

The method must take no arguments and return void. class_name must be a valid SQL Server identifier and must exist as a class in the assembly with assembly visibility. If the class has a namespace-qualified name that uses ‘.’ to separate namespace parts, the class name must be delimited by using [ ] or ” ” delimiters. The class cannot be a nested class.