Complete syntax and parameter description of various triggers in SQL SERVER

Source: Internet
Author: User

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 ] [ , ] [ 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_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 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

Name of the architecture of the DML trigger. 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

Trigger name. 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 DML triggers instead of triggering SQL statementsTherefore, the priority is higher than the operation that triggers the 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 to modify 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 an instead of trigger, the DELETE option is not allowed 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

Name of the predefined event group in the Transact-SQL language. 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 add the event types it coversSys. trigger_eventsDirectory view as a macro.

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.

DML triggers use deleted and inserted logical (concept) tables. They are structured like tables that define triggers, that is, tables that attempt to perform user operations on them. InDeletedAndInsertedThe table stores the old or new values of rows that may be changed by users. For example, to retrieve all values in the deleted table, use:

SELECT *
FROM deleted

DDL and logon triggers use the EVENTDATA (Transact-SQL) function to obtain information about triggering events.

In a DELETE, INSERT, or UPDATE trigger, if the compatibility level is set to 70, SQL Server cannotInsertedAndDeletedTable existsText,NtextOrImageColumn reference. InaccessibleInsertedAndDeletedTableText,NtextAndImageValue. To retrieve the new value in the INSERT or UPDATE triggerInsertedJoin the table with the original update table. If the compatibility level is equal to or less than 65InsertedOrDeletedOfText,NtextOr a Null value is allowed.ImageThe column returns a Null value. 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 allows update through the INSTEAD trigger OF the table or view.Text,NtextOrImageColumn.

<Method_specifier>

For CLR triggers, specify the method for binding an assembly to the trigger. This method cannot contain any parameters and must return NULL values. Class_name must be a valid SQL Server identifier and the class must exist in the visible assembly. If there is a qualified namespace name that uses "." To separate the namespace, the class name must be separated by a [] or "" separator. This class cannot be a nested class.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.