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
    rollback transaction
    print "You cannot delete any publishers!"

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: