How to create a trigger in SQL Server Enterprise Manager

Source: Internet
Author: User
Tags bitmask comparison insert reference
server|sqlserver| Triggers | creating


The following describes how to create triggers using SQL Server Administration Tools Enterprise Manager and Transaction_sql in MS SQL Server.
You must consider the following aspects before creating triggers:

The CREATE TRIGGER statement must be the first statement of the batch process;
The owner of the table has the default permission to create the trigger, and the table owner cannot pass that permission on to another user;
Triggers are database objects, so their naming must conform to naming rules;
Although you can reference objects in other databases in the SQL statement of a trigger, triggers can only be created in the current database;
Although triggers can reference views or temporary tables, they cannot create triggers on a view or temporary table, only on a base table or on a table that creates a view;
A trigger can only correspond to a table, which 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 statements cannot trigger inserts or update types.
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.

Creating 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.

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




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.