SQL Server Trigger syntax

Grammar:
Trigger on the INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [schema_name.] Trigger_name
On {table | view}
[With <dml_trigger_option> [,... N]]
{for | After | INSTEAD of}
{[INSERT] [,] [UPDATE] [,] [DELETE]}
[With APPEND]
[Not for REPLICATION]
as {sql_statement [;] [,... N] | EXTERNAL NAME <method specifier [;] >}

<dml_trigger_option>:: =
[Encryption]
[
EXECUTE as Clause]

<method_specifier>:: =
Assembly_name.class_name.method_name


Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
On {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


Trigger on a logon event (logon Trigger)
CREATE TRIGGER trigger_name
on all SERVER
[With <logon_trigger_option> [,... N]]
{for| After} LOGON
as {sql_statement [;] [,... N] | EXTERNAL NAME < method specifier > [;]}
<logon_trigger_option>:: =
[Encryption]
[EXECUTE as Clause]

<method_specifier>:: =
Assembly_name.class_name.method_name

Parameters:

Schema_name

The name of the schema to which the DML trigger belongs. The scope of a DML trigger is the schema of the table or view for which the trigger is created. Schema_name cannot be specified for DDL or logon triggers.

Trigger_name

The name of the trigger. Trigger_name must follow the rules for identifiers, but trigger_name cannot start with # or # #.

Table | View

The table or view on which the DML trigger is executed, sometimes referred to as a trigger table or a trigger view. You can specify the fully qualified name of a table or view as needed. Views can only be referenced by INSTEAD of triggers. DML triggers cannot be defined on local or global temporary tables.

DATABASE

Applies the scope of the DDL trigger to the current database. If this parameter is specified, the trigger is fired whenever a event_type or event_group appears in the current database.

All SERVER

Applies the scope of the DDL or logon trigger to the current server. If this parameter is specified, the trigger is fired whenever a event_type or event_group occurs at any location in the current server.

With encryption

Blurs the text of the CREATE TRIGGER statement. Use with encryption to prevent a trigger from being published as part of SQL Server replication. You cannot specify a with encryption for a CLR trigger.

EXECUTE as

Specifies the security context that is used to execute the trigger. Allows you to control the user account that is used by the SQL Server instance to validate permissions on any database object referenced by the trigger.

for | After

After the specified DML trigger is triggered only when all operations specified in the triggering SQL statement have been executed successfully. All referential cascade operations and constraint checks must also be completed successfully before firing this trigger.

If only the FOR keyword is specified, after is the default value.

You cannot define an after trigger on a view.

INSTEAD of

Specifies that the DML trigger is executed instead of triggering the SQL statement, so that it takes precedence over the action of the triggering statement. INSTEAD of cannot be specified for DDL or logon triggers.

For a table or view, you can define a maximum of INSTEAD of triggers per INSERT, UPDATE, or DELETE statement. However, you can define views for multiple views that have their own INSTEAD of triggers.

INSTEAD of triggers cannot be used for updatable views that use with CHECK OPTION. If you add a INSTEAD of trigger to an updatable view that specifies with CHECK OPTION, SQL Server throws an error. The user must delete this option with ALTER VIEW to define the INSTEAD of trigger.

{[DELETE] [,] [INSERT] [,] [UPDATE]}

Specifies data modification statements that can be activated when a DML trigger attempts to this table or view. You must specify at least one option. Allows any combination of the above options to be used in the trigger definition.

For INSTEAD of triggers, the delete option is not allowed on tables that have a referential relationship with the specified cascade operation on DELETE. Similarly, the update option is not allowed on tables that have a reference relationship with the specified cascade operation on UPDATE.

Event_type

The name of the Transact-SQL language event that will cause the DDL trigger to fire after execution. Valid events for DDL triggers are listed in the DDL event.

Event_group

The name of the pre-defined Transact-SQL language event grouping. DDL triggers are fired after any Transact-SQL language events that belong to Event_group are executed. A valid event group for DDL triggers is listed in the DDL Event Group.

After the CREATE TRIGGER is finished running, Event_group can also be used as a macro by adding the event types it covers to the Sys.trigger_events catalog view.

With APPEND

Specifies that a trigger for an existing type should be added again. Use this optional clause only if the compatibility level is equal to or less than 65 o'clock. If the compatibility level is equal to or greater than 70, you do not need to use the WITH APPEND clause to add other triggers of an existing type. This is the default behavior for CREATE TRIGGER with compatibility level set equal to or greater than 70. With APPEND cannot be used with INSTEAD of triggers. If you explicitly declare an after trigger, you cannot use the clause. The with APPEND is only used when a for is specified for backward compatibility (but not INSTEAD of or after). If you specify EXTERNAL NAME (that is, the trigger is a CLR trigger), you cannot specify with APPEND.

Not for REPLICATION

Indicates that a trigger should not be executed when the replication agent modifies a table that involves a trigger. For more information, see Using Not for REPLICATION to control constraints, identities, and triggers.

Sql_statement

Trigger conditions and actions. The trigger condition specifies additional criteria that determine whether the attempted DML, DDL, or logon event causes the trigger action to be performed.

When you try the above operation, the trigger action specified in the Transact-SQL statement is executed.

Triggers can contain any number and kind of Transact-SQL statements, but there are exceptions. The purpose of a trigger is to inspect or change the data based on data modifications or definition statements, and it should not return data to the user. Transact-SQL statements in triggers often contain control-flow languages.

DML triggers use the deleted and inserted logical (concept) tables. They are structurally similar to the table that defines the trigger, which is the table on which the user action was attempted. The deleted and inserted tables save old or new values for rows that may be changed by the user. For example, to retrieve all the values in the deleted table, use:

SELECT *
From deleted

DDL and logon triggers obtain information about the triggering event by using the EVENTDATA (Transact-SQL) function.

In the DELETE, INSERT, or UPDATE trigger, if the compatibility level is set to 70, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables. You cannot access the text, ntext, and image values in the inserted and deleted tables. To retrieve the new value in the INSERT or UPDATE trigger, join the inserted table with the original Update table. If the compatibility level is equal to or less than 65, a null value is returned for inserted or deleted text, ntext, or an image column that allows null values, or a 0-length string if the column cannot be null.

If the compatibility level is equal to or higher than 80,sql Server will allow the text, ntext, or image columns to be updated through the INSTEAD of triggers of the table or view.

< method_specifier>

For CLR triggers, specifies the method that the assembly binds to the trigger. The method cannot have any parameters and must return a null value. Class_name must be a valid SQL Server identifier, and the class must exist in the visible assembly. If the class has a use of "." To delimit namespace-qualified names for the namespace section, the class name must be delimited by the [] or "" delimiter. The class cannot be a nested class.