Complete syntax and parameter descriptions for various types of triggers in SQL Server _mssql

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] [don't for REPLICATION] as {sql_statement [;] [,... n] | EXTERNAL NAME <method specifier [;] >}<dml_trigger_option>:: =[Encryption] [EXECUTE as Clause]<method_specifier>:: =Assembly_name.Class_name.Method_nameTrigger 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_nameTrigger on a logon event (logon Trigger)CREATE TRIGGER trigger_name on all servers [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 login triggers.

Trigger_name

the name of the trigger . Trigger_name must follow the rules for identifiers, but trigger_name cannot begin 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 the table or view as needed. Views can only be referenced by INSTEAD of triggers. You cannot define a DML trigger on a local or global temporary table.

DATABASE

applies the scope of a DDL trigger to the current database . If this parameter is specified, the trigger is fired whenever Event_type or event_group appear in the current database.

All SERVER

Applies the scope of the DDL or login trigger to the current server. If this parameter is specified, the trigger is fired whenever event_type or event_group appear anywhere on the current server.

With encryption

The text of the CREATE TRIGGER statement is obfuscated. 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 used to execute the trigger. Allows you to control the user account that the SQL Server instance uses to authenticate the permissions of any database object referenced by the trigger.

for | After

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

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

You cannot define after triggers on a view.

INSTEAD of

specifies that the DML trigger is executed instead of triggering the SQL statement , and therefore has precedence over the action of the triggering statement. Cannot specify INSTEAD of for DDL or login triggers.

For a table or view, each INSERT, UPDATE, or DELETE statement can define a INSTEAD of trigger. However, you can define views for multiple views with your own INSTEAD of triggers.

INSTEAD of triggers cannot be used with updatable views using the 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 enable the DML trigger to activate the table or view when it is tried. At least one option must be specified. An arbitrary combination of the above options is allowed in the trigger definition.

For INSTEAD of triggers, the DELETE option is not allowed on tables with reference relationships with the specified cascade action on DELETE. Similarly, the update option is not allowed for tables with reference relationships 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 predefined group of Transact-SQL language events . DDL triggers are fired after any of the Transact-SQL language events belonging to Event_group are executed. The group of valid events for DDL triggers is listed in the DDL Event Group.

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

With APPEND

Specifies that a trigger of an existing type should be added again. Use this optional clause only if the compatibility level is equal to or below 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 of the CREATE TRIGGER with the compatibility level setting equal to or higher than 70. With APPEND cannot be used with INSTEAD of triggers. If you explicitly declare an after trigger, you cannot use the clause either. The with APPEND is used only when a for is specified for backward compatibility (but no INSTEAD of or after). If EXTERNAL NAME is specified (that is, the trigger is a CLR trigger), the with APPEND cannot be specified.

Not for REPLICATION

Indicates that a trigger should not be executed when a replication agent modifies a table that involves a trigger. For more information, see Controlling Constraints, identities, and triggers using the not for REPLICATION.

Sql_statement

trigger conditions and actions . Trigger conditions specify additional criteria that determine whether the attempted DML, DDL, or logon event causes a trigger operation to occur.

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

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

DML triggers use the deleted and inserted logical (concept) tables . They are structurally similar to tables that have a trigger defined, that is, the table on which they are trying to perform user action. The deleted and inserted tables hold the old or new values of the 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 use the EVENTDATA (Transact-SQL) function to obtain information about the triggering event.

In the DELETE, INSERT, or UPDATE trigger, if the compatibility level is set to 70, SQL Server does not allow text, nte in the inserted and deleted tables A XT or image column reference. text,ntext , and image values in the inserted and deleted tables cannot be accessed. 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, the text of inserted or deleted ,ntext , or an image that allows Null values column returns a null value, 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 with the INSTEAD of triggers for the table or view.

< method_specifier>

For CLR triggers, specifies the method that the assembly binds to the trigger. The method cannot have any arguments and must return a null value. The class_name  must be a valid SQL Server identifier, and the class must exist in the visible assembly. If the class has one that uses the "." To separate namespace-qualified names for the namespace part, the class name must be delimited with the [] or "" delimiter. The class cannot be a nested class.