Shulou(Shulou.com)05/31 Report--

This article introduces the complete syntax and parameters of all kinds of triggers in SQL SERVER. The content is very detailed. Interested friends can use it for reference. I hope it will be helpful to you.

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 [,... 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_nameTrigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) CREATE TRIGGER trigger_name ON {ALL SERVER | DATABASE} [WITH [,... n]] {FOR | AFTER} {event_type | event_group} [,... n] AS {sql_statement [ ] [,... n] | EXTERNAL NAME

[;]}:: = [ENCRYPTION] [EXECUTE AS Clause]:: = assembly_name.class_name.method_nameTrigger on a LOGON event (Logon Trigger) CREATE TRIGGER trigger_name ON ALL SERVER [WITH [,... n]] {FOR | AFTER} LOGON AS {sql_statement [;] [,... n] | EXTERNAL NAME

[;]}:: = [ENCRYPTION] [EXECUTE AS Clause]:: = 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 was created. For DDL or login triggers, schema_name cannot be specified.

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 called the trigger table or 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. 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 fires whenever an event_type or event_group appears 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 fires whenever an event_type or event_group appears anywhere in the current server.

WITH ENCRYPTION

Obfuscate the text of the CREATE TRIGGER statement. Using WITH ENCRYPTION prevents triggers from being published as part of SQL Server replication. WITH ENCRYPTION cannot be specified for CLR triggers.

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 validate the permissions of any database object referenced by the trigger.

FOR | AFTER

AFTER specifies that the DML trigger is triggered only if all actions specified in the trigger SQL statement have been successfully performed. All reference cascading operations and constraint checks must also be completed successfully before firing this trigger.

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

You cannot define AFTER triggers on a view.

INSTEAD OF

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

For tables or views, each INSERT, UPDATE, or DELETE statement can define at most one INSTEAD OF trigger. 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 an INSTEAD OF trigger to an updatable view with a WITH CHECK OPTION specified, SQL Server throws an error. The user must delete this option with ALTER VIEW before defining INSTEAD OF triggers.

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

Specifies data modification statements that activate the DML trigger when it attempts the table or view. At least one option must be specified. Any sequential combination of the above options is allowed in the trigger definition.

For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a reference relationship with the specified cascading operation ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a reference relationship to the specified cascading operation ON UPDATE.

Event_type

The name of the Transact-SQL language event that causes 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 Transact-SQL language event group. After any Transact-SQL language event belonging to event_group is executed, the DDL trigger is fired. Valid event groups for DDL triggers are listed in the DDL event group.

After CREATE TRIGGER has run, 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 an existing type of trigger should be added. This optional clause is required only if the compatibility level is equal to or below 65. If the compatibility level is 70 or higher, 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 settings equal to or higher than 70. WITH APPEND cannot be used with INSTEAD OF triggers. This clause cannot be used if an AFTER trigger is explicitly declared. WITH APPEND can be used only if FOR is specified for backward compatibility (but there is no INSTEAD OF or AFTER). 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 using NOT FOR REPLICATION to Control constraints, identities, and triggers.

Sql_statement

Trigger conditions and actions. The trigger condition specifies additional criteria for determining 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 variety of Transact-SQL statements, but there are exceptions. The purpose of a trigger is to check or change data based on data modification or definition statements; it should not return data to the user. Transact-SQL statements in triggers often contain control flow languages.

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

SELECT * FROM deleted

DDL and login triggers get information about the triggered event by using the EVENTDATA (Transact-SQL) function.

In DELETE, INSERT, or UPDATE triggers, SQL Server does not allow text, ntext, or image column references in inserted and deleted tables if the compatibility level is set to 70. You cannot access the text, ntext, and image values in the inserted and deleted tables. To retrieve a new value in an 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 Null value is returned for the text of inserted or deleted, ntext, or image column that allows Null values; if the column cannot be Null, a zero-length string is returned.

If the compatibility level is equal to or higher than 80 SQL Server, text, ntext, or image columns are allowed to be updated through the INSTEAD OF trigger of the table or view.

For CLR triggers, specify how the assembly is bound to the trigger. The method cannot take any parameters 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 one of the classes uses "." To separate the namespace qualified name of the part of the namespace, the class name must be separated by the [] or "" separator. This class cannot be a nested class.

The complete syntax and parameters of all kinds of triggers in SQL SERVER are shared here. I hope the above content can be helpful to everyone and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.