MS SQL Basics Tutorial: Creating triggers

This article describes the concepts, roles, and basic issues of triggers, and we'll describe how to use SQL Server Administration Tools Enterprise Manager and Transaction_sql in MS SQL Server to create triggers separately.

You must consider the following aspects before creating triggers:

The Create TRIGGER statement must be the first statement of the batch; the owner of the table has the default permission to create the trigger, and the table owner cannot pass the permission on to another user; the trigger is a database object, so its naming must conform to the naming convention; In a statement, you can refer to objects in other databases. However, triggers can only be created in the current database, although triggers may reference views or temporary tables, but cannot create triggers on views or temporary tables, only on base tables or on tables that create views, and a trigger can only correspond to one table. This is determined by the mechanism of the trigger; Although the TRUNCATE TABLE statement is like a DELETE statement without a WHERE clause, the statement cannot trigger a DELETE trigger because the TRUNCATE TABLE statement is not logged; WRITETEXT A statement cannot trigger an INSERT or update type.

When you create a trigger, you must specify the name of the trigger, the table on which to define the trigger, and activate the trigger's modified statement, such as INSERT, DELETE, UPDATE. Of course two or three different modification statements can also trigger the same trigger, such as INSERT and UPDATE statements that activate the same trigger.

12.6.1 create triggers with administrative tools Enterprise Manger

The steps are as follows:

Start Enterprise Manger and log on to the specified server. Expand the database, and then expand the database that contains the table on which you want to create the trigger, and then click the table. Right-click, select All Tasks in the pop-up menu, and then click Manage Triggers .... Select New in the Name box, and enter the trigger text in the text box, as shown in Figure 12-5. Click the check Syntax to check that the statement is correct. Click Apply to have the newly created trigger name in the name Drop-down list. Click OK to close the window to create success.

12.6.2 create a trigger with the Create TRIGGER command

The parameters are described as follows:

Trigger_name

is the name of the trigger that the user is creating, must conform to the naming rules for MS SQL Server, and its name must be unique in the current database. Table

is the name of the table associated with the user-created trigger, and the table already exists. With encryption

Indicates that the syscomments table containing the Create TRIGGER text is encrypted. After

Indicates that the trigger is activated only after the specified action (INSERT, DELETE, UPDATE) is executed, executing the SQL statement in the trigger. If the keyword for is used, it is represented as an after trigger, and the type trigger can only be created on the table. INSTEAD of

See "12.8 INSTEAD of Triggers" [DELETE] [,] [INSERT] [,] [UPDATE]

The keyword is used to indicate which data operation will activate the trigger. You must specify at least one option, in which the order of the three is unrestricted, and the options are separated by commas. With APPEND

Indicates that another type of trigger already exists is added. This option is only used if the compatibility level (refers to a database behavior with previous versions of MS SQL Server compatibility) is not greater than 65 o'clock. Not for REPLICATION

Indicates that the trigger cannot be executed when the copy process modifies the table associated with the trigger. As

Is the action that the trigger will perform. Sql_statement

is a conditional statement or processing statement that is contained in a trigger. The trigger's conditional statement defines additional criteria for determining whether the insert, DELETE, UPDATE statement that will be executed activates the trigger. IF UPDATE (column)

Used to determine if a certain column is an INSERT or update operation, but not with a delete operation. IF (columns_updated ())

Used only in triggers for INSERT and update types to check whether the column involved is updated or inserted. Bitwise_operatorj

Is the bit logical operator used in the comparison. Pdated_bitmask

Is the reshaping bitmask of the columns that are updated or inserted. For example, if the table T includes C1, C2, C3, C4, and C5 five columns. In order to determine whether only the C2 column has been modified, and then use the bitmask, if you want to determine whether C1, C2, c3,c4 are modified, you can use 14来 to do bit masks. Comparison_operator

is a comparison operator with "=" to check whether all columns defined in Updated_bitmask are updated, and ">" to check that some of the columns defined in Updated_bitmask are updated. Column_bitmask

Bitmask of those columns that are checked for updates.

See the full set of "MS SQL Basics Tutorials"