You can create a trigger with either Enterprise Manager or Transact-SQL.
To use the Enterprise Manager to create triggers:
Right-click the name of the table that the trigger will be assigned to.
Choose the All Tasks | Manage Triggers menu option.
Enter the appropriate Transact-SQL statements in the Trigger Properties dialog box, shown below:
Creating a new trigger
Creating Triggers
A trigger can be fi red for any combination of insert, update, or delete events. Triggers are
created and modifi ed with the standard DDL commands, CREATE, ALTER, and DROP, as follows:
CREATE TRIGGER Schema.TriggerName ON Schema.TableName
AFTER | INSTEAD OF [Insert, Update, (and or) Delete]
AS Trigger Code;
You can also create, view and modify triggers using Management Studio’s Object Explorer, as shown in Figure 4-5.
Figure 4-5: Object Explorer lists all triggers for any table and may be used to modify the trigger using
the context menu.
Although you can use Enterprise Manager to create and modify triggers, you will need to use Transact-SQL statements within Enterprise Manager to specify your options.
Creating triggers with Transact-SQL
To create a trigger with Transact-SQL, you will use the CREATE TRIGGER Transact-SQL statement.
The syntax for this statement is shown in the following series of images.
1) trigger_name is the name of the trigger you are going to create. A trigger is a database object,
so it must have a unique name within the database 2) table is the name of the table on which you will create the trigger.
3) WITH ENCRYPTION encrypts the text so that it can not be read from the syscomments table.
4) trigger_type is the type of trigger you wish to create.
The type can be INSERT, UPDATE, or DELETE, depending on the type of trigger you are creating.
5) WITH APPEND is an optional statement that indicates to SQL Server that if a trigger of the same type already exists,
this trigger should be added.
6) NOT FOR REPLICATION indicates that any replication operations that affect data in the table should not fir the trigger.
7) column is used if you wish to test for a specific column to have data inserted or updated into it. Multiple columns can be specified.
8) Bitwise_operator, updated_bitmask, comparison_operator, and column_bitmask all are affected by the COLUMNS_UPDATED clause, which is used to test a bit-filed representing one or more columns that are updated.
9)trigger_code is the Transact-SQL code that you will use to implement your business rules, using the INSERTED and
DELETED special trigger tables.
In the next lesson, you will learn how to create triggers that satisfy unique business rules.
