Page 84 - DCAP508_DATABASE_ADMINISTRATION

Database Administration

Notes Multiple triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple,
different actions to take place in response to the same modification statement.

Triggers Compared to Constraints

Constraints and triggers each have benefits that make them useful in special situations. The
primary benefit of triggers is that they can contain complex processing logic that uses Transact-
SQL code. Therefore, triggers can support all of the functionality of constraints; however, triggers
are not always the best method for a given feature.
Entity integrity should always be enforced at the lowest level by indexes that are part of PRIMARY
KEY and UNIQUE constraints or are created independently of constraints. Domain integrity should
be enforced through CHECK constraints, and Referential Integrity (RI) should be enforced through
FOREIGN KEY constraints, assuming their features meet the functional needs of the application.

Triggers are most useful when the features supported by constraints cannot meet the functional
needs of the application. For example:

FOREIGN KEY constraints can validate a column value only with an exact match to a value
in another column, unless the REFERENCES clause defines a cascading referential action.
A CHECK constraint can validate a column value only against a logical expression or
another column in the same table. If your application requires that a column value be
validated against a column in another table, you must use a trigger.

Constraints can communicate about errors only through standardized system error
messages. If your application requires (or can benefit from) customized messages and
more complex error handling, you must use a trigger.

Triggers can cascade changes through related tables in the database; however, these changes can
be executed more efficiently through cascading referential integrity constraints.

Triggers can disallow or roll back changes that violate referential integrity, thereby
canceling the attempted data modification. Such a trigger might go into effect when you
change a foreign key and the new value does not match its primary key. For example, you
can create an insert trigger on titleauthor.title_id that rolls back an insert if the new value
does not match some value in titles.title_id. However, FOREIGN KEY constraints are
usually used for this purpose.
If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger
execution but prior to the AFTER trigger execution. If the constraints are violated, the
INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed

Creating Trigger

Before you create a trigger, consider that:

The CREATE TRIGGER statement must be the first statement in the batch. All other
statements that follow in that batch are interpreted as part of the definition of the CREATE
TRIGGER statement.

Permission to create triggers defaults to the table owner, who cannot transfer it to other
users.

Triggers are database objects, and their names must follow the rules for identifiers.
You can create a trigger only in the current database, although a trigger can reference
objects outside of the current database.

78 LOVELY PROFESSIONAL UNIVERSITY