Triggers: Enforce Referential Integrity

You can use triggers to perform a number of automatic actions, such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications, and maintaining the referential integrity of data across a database.

Triggers are automatic no matter what caused the data modification—a clerk’s data entry or an application action. A trigger is specific to one or more data modification operations (update, insert, and delete), and is executed once for each SQL statement.

For example, to prevent users from removing any publishing companies from the publishers table, you could use:

create trigger del_pub
on publishers
for delete
as
begin
    rollback transaction
    print "You cannot delete any publishers!"
end

The next time someone tries to remove a row from the publishers table, the del_pub trigger cancels the deletion, rolls back the transaction, and prints a message.

A trigger “fires” only after the data modification statement has completed and SAP ASE has checked for any datatype, rule, or integrity constraint violation. The trigger and the statement that fires it are treated as a single transaction that can be rolled back from within the trigger. If SAP ASE detects a severe error, the entire transaction is rolled back.

Use triggers to:

Cascade changes through related tables in the database. For example, a delete trigger on the title_id column of the titles table can delete matching rows in other tables, using the title_id column as a unique key to locating rows in titleauthor and roysched.
Disallow, or roll back, changes that would violate referential integrity, canceling the attempted data modification transaction. Such a trigger might go into effect when you try to insert a foreign key that does not match its primary key. For example, you could create an insert trigger on titleauthor that rolled back an insert if the new titleauthor.title_id value did not have a matching value in titles.title_id.
Enforce restrictions that are much more complex than those that are defined with rules. Unlike rules, triggers can reference columns or database objects. For example, a trigger can roll back updates that attempt to increase a book’s price by more than 1 percent of the advance.
Perform simple “what if” analyses. For example, a trigger can compare the state of a table before and after a data modification and take action based on that comparison.

Use Triggers Versus Integrity Constraints
As an alternative to using triggers, you can use the referential integrity constraint of the create table statement to enforce referential integrity across tables in the database.
Create Triggers
A trigger is a database object. When you create a trigger, you specify the table and the data modification commands that should fire, or activate, the trigger. Then, you specify any actions the trigger is to take.
Use Triggers to Maintain Referential Integrity
Triggers maintain referential integrity, which assures that vital data in your database—such as the unique identifier for a given piece of data—remains accurate and can be used consistently as other data in the database changes. Referential integrity is coordinated through the use of primary and foreign keys.
Multirow Considerations
Multirow considerations are particularly important when the function of a trigger is to recalculate summary values, or provide ongoing tallies.
Roll Back Triggers
You can roll back triggers using either the rollback trigger statement or the rollback transaction statement (if the trigger is fired as part of a transaction). However, rollback trigger rolls back only the effect of the trigger and the statement that caused the trigger to fire; rollback transaction rolls back the entire transaction.
Global Login Triggers
Use sp_logintrigger to set a global login trigger that is executed at each user login. To take user-specific actions, use alter login or create login to set a user-specific login trigger.
Nesting Triggers
Triggers can nest to a depth of 16 levels. The current nesting level is stored in the @@nestlevel global variable.
Rules Associated with Triggers
In addition to anticipating the effects of a multirow data modification, trigger rollbacks, and trigger nesting, there are other factors to consider when you write triggers, such as permissions, restrictions, and performance.
Disable Triggers
To disable triggers during bulk insert, update, or delete operations, use the disable trigger option of the alter table command.
Drop Triggers
You can remove a trigger by dropping it or by dropping the trigger table with which it is associated.
Multiple Triggers
You can create up to 50 different triggers on a table for each command (insert, update, and delete), as well as specify the order in which the triggers are fired after statement execution by using the order parameter in the create trigger command. You can create multiple triggers without an order clause.
Get Information About Triggers
As database objects, triggers are listed in sysobjects by name. The type column of sysobjects identifies triggers with the abbreviation “TR”.
instead of Triggers
instead of triggers are special stored procedures that override the default action of a triggering statement (insert, update, and delete), and perform user-defined actions.