Complete SQL Server triggers syntax and parameter description (extended)

Syntax: trigpolicaninsert, UPDATE, orDELETEstatementtoatableorview (DMLTrigger) CREATETRIGGER [schema_name.] trigger_nameON {table | view} [WITHdml_trigger_option [,... n] {FOR | AFTER | INSTEADOF} {[INSERT] [,] [

Syntax: Trigger on an 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] [,] [

Syntax: Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger) create trigger [schema_name.] trigger_name ON {table | view} [ [,... N] {FOR | AFTER | instead of} {[INSERT] [,] [UPDATE] [,] [DELETE]} [with append] [not for replication] AS {SQL _statement [;] [,... n] | EXTERNAL NAME } : = [ENCRYPTION] [execute as Clause] : = 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} [ [,... N] {FOR | AFTER} {event_type | event_group }[,... n] AS {SQL _statement [;] [,... n] | external name <method specifier> [;]} : = [ENCRYPTION] [execute as Clause] : = Assembly_name.class_name.method_name
Trigger on a LOGON event (Logon Trigger) create trigger trigger_name on all server [ [,... N] {FOR | AFTER} logon as {SQL _statement [;] [,... n] | EXTERNAL NAME <method specifier> [;]} : = [ENCRYPTION] [execute as Clause] ::= Assembly_name.class_name.method_name parameter: schema_name

Name of the architecture to which the DML trigger belongs. The DML trigger scope is the architecture 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 identifier rules, but trigger_name cannot start with # Or.

Table | view

The table or view for which DML triggers are executed, sometimes called the trigger table or trigger view. You can specify a fully qualified name for a table or view as needed. A view can only be referenced by an instead of trigger. DML triggers cannot be defined for local or global temporary tables.

DATABASE

Apply the DDL trigger scope to the current database. If this parameter is specified, the trigger is triggered as long as event_type or event_group appears in the current database.

ALL SERVER

Apply the DDL or login trigger scope to the current server. If this parameter is specified, the trigger is triggered as long as event_type or event_group appears at any location on the current server.

WITH ENCRYPTION

Blur the text of the create trigger statement. With encryption can prevent the trigger from being published as part of SQL Server replication. With encryption cannot be specified for CLR triggers.

EXECUTE

Specifies the security context used to execute the trigger. Allows you to control the user account used by the SQL Server instance to verify the permissions of any database objects referenced by the trigger.

FOR | AFTER

AFTER, a dml trigger is triggered only when all the operations specified in the SQL statement are successfully executed. All reference cascade operations and constraints check must also be completed before the trigger is triggered.

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

You cannot define an AFTER trigger for a view.

INSTEAD

Specify to execute a DML trigger instead of triggering an SQL statement. Therefore, the priority is higher than that of the trigger statement. You cannot specify instead of for DDL or logon triggers.

For tables or views, each INSERT, UPDATE, or DELETE statement can define up to one INSTEAD trigger. However, you can define views for multiple views with your own instead of triggers.

The instead of trigger cannot be used to use the updatable view with check option. If you add the instead of trigger to the updatable view with check option specified, SQL Server will cause an error. You must use alter view to delete this option before defining the instead of trigger.

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

Specifies a data modification statement. These statements can be activated when a DML trigger attempts this table or view. You must specify at least one option. You can use any sequential combination of the preceding options in the trigger definition.

For the instead of trigger, the Hong Kong VM does not allow the DELETE option to be used for tables with reference relationships for the specified cascade operation on delete. Similarly, the UPDATE option is not allowed for tables with reference relationships for the specified cascade operation on update.

Event_type

The name of the Transact-SQL language event that triggers the DDL trigger after execution. DDL events list the valid events of DDL triggers.

Event_group

The name of the predefined event group. After you execute any event in the event group's Transact-SQL language, the DDL trigger is triggered. The DDL Event Group lists the valid event groups of the DDL trigger.

After the create trigger operation is complete, event_group can also be used as a macro by adding the event types it covers to the sys. trigger_events directory view.

WITH APPEND

Specify that an existing trigger should be added. This optional clause is required only when the compatibility level is equal to or less than 65. If the compatibility level is equal to or higher than 70, you do not need to use the with append clause to add other triggers of the existing type. This is the default action of the create trigger with the compatibility level set equal to or greater than 70. With append cannot be used WITH the instead of trigger. If an AFTER trigger is explicitly declared, this clause cannot be used. With append can be used only when FOR (but not instead of or AFTER) is specified FOR backward compatibility. If external name is specified (that is, the trigger is a CLR trigger), with append cannot be specified.

NOT FOR REPLICATION

Indicates that the trigger should not be executed when the replication agent modifies the table involving the trigger. FOR more information, see use not for replication to control constraints, identifiers, and triggers.

SQL _statement

Trigger conditions and operations. Specify other criteria for the trigger condition to determine whether the attempted DML, DDL, or logon event causes trigger operations.

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

A trigger can contain any number and types of Transact-SQL statements. A trigger is used to check or modify data based on data modification or definition statements. It should not return data to users. The Transact-SQL statement in a trigger often contains the control flow language.