Visual Basic Concepts

Article
01/04/2007

Triggers in the SQL Editor

A trigger is a special kind of stored procedure that goes into effect when you modify data in a specified table using one or more data modification operations: UPDATE, INSERT, or DELETE. Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you could control whether to allow an order to be inserted based on a customer's current account status.

Triggers are also useful for enforcing referential integrity, which preserves the defined relationships between tables when you add, update, or delete the rows in those tables. However, the best way to enforce referential integrity is to define primary key and foreign key constraints in the related tables. If you use database diagrams, you can create a relationship between tables to automatically create a foreign key constraint.

Advantages of Using Triggers

Triggers are useful in these ways:

Triggers are automatic: they are activated immediately after any modification to the table's data, such as a manual entry or an application action.
Triggers can cascade changes through related tables in the database. For example, you can write a delete trigger on the title_id column of the titles table to cause a deletion of matching rows in other tables. The trigger uses the title_id column as a unique key to locate matching rows in the titleauthor, sales, and roysched tables.
Triggers can enforce restrictions that are more complex than those defined with check constraints. Unlike check constraints, triggers can reference columns in other tables. For example, a trigger can roll back updates that attempt to apply a discount (stored in the discounts table) to books (stored in the titles table) with a price of less than $10.

Creating a Trigger

A trigger is a database object that you create by specifying:

The current table.
The data modification transactions that activate the trigger: adding new data (INSERT), updating existing data (UPDATE), or deleting existing data (DELETE).
The actions that the trigger will take immediately following the transactions you specified.

You write triggers in Transact-SQL for Microsoft^® SQL Server™ databases or PL/SQL for Oracle databases.

To create a trigger

In Data View (available from the Standard toolbar or the View menu), expand the Tables folder.
Right-click the name of the table that you want to create a trigger on. Choose New Trigger from the shortcut menu.

A new trigger is created with the following SQL statements already defined for you:
```
Create Trigger /*Trigger_Name*/
on /*Table_name*/
For /*Insert, Update, Delete*/
As
  print 'Trigger Fired'
```
Modify the trigger text as follows:

Line	Replace	With
1	`/Trigger_Name/`	The name you want to assign to the trigger
2	`/Table_name/`	The name of the table you want to attach the trigger to
3	`/Insert, Update, Delete/`	The type of transactions that will activate this trigger

For example, to create a trigger named `employee_insupd` for insert and update transactions on the `employee` table, you would change the first three lines of the trigger text to the following: Create Trigger employee_insupd on employee For Insert, Update

Write the remaining trigger text in SQL.

For examples of triggers for Microsoft SQL Server databases, see "Creating a Trigger" in the SQL Server documentation. For details about the Transact-SQL syntax of triggers, see the "CREATE TRIGGER" statement in the SQL Server documentation.

Opening a Trigger

You can open a trigger to view or edit the text of an existing trigger that is stored in your database. Triggers are scripted in Transact-SQL for Microsoft^® SQL Server™ databases or PL/SQL for Oracle databases.

To open a trigger

In Data View, expand the Tables folder.
Expand the table whose trigger you want to open.
Right-click the name of the trigger that you want to open and click Design on the shortcut menu.

-or-

Double-click the name of the trigger that you want to open.

Saving a Trigger

To add a new trigger to the database or to update an existing trigger that you have modified, you can save a trigger.

To save a trigger

In the SQL Editor, choose Save to Database from the File menu.
If you are updating an existing trigger, a message box prompts you to confirm the save action. Choose Yes.

A saved trigger appears in the Tables folder in Data View under the table that it's attached to.

Deleting a Trigger

To disable the actions defined in the trigger that are automatically carried out on your database immediately following the specified transactions, you can delete a trigger.

You might also want to delete any triggers that enforce referential integrity between related tables if you use database diagrams to design your database. Database diagrams use relationships instead of triggers for this purpose. Thus, if a trigger duplicates a relationship in a database diagram, you should delete either the trigger or the relationship.

To delete a trigger

In Data View, expand the Tables folder.
Expand the table whose trigger you want to delete.
Right-click the trigger that you want to delete and choose Delete from the shortcut menu.
A message prompts you to confirm the deletion. Choose Yes.

The trigger is deleted from the database and Data View.

Partager via