Databases Reference
In-Depth Information
FOR EXAMPLE
Security through Triggers
Your database includes several tables that contain sensitive information. A
small number of employees are allowed to view the information, but no
employees are authorized to directly modify the information. You've set
access limits, but you need to audit attempts to change the data in the tables.
This is a situation where you could use triggers as a security tool. You
would create DML triggers on each of the tables that execute when a user
attempts to modify or delete table data. You configure the trigger to run
instead of the triggering statement. You could then have the trigger write
information about the attempt to a separate audit table. You could include
not only that the action occurred, but also what the employee attempted to
do, when, and even which employee made the attempt.
statement. With a few DBMSs, including SQL Server 2005, you can also have trig-
gers that fire when database or database server objects are created or modified,
such as a trigger that fires any time you create a table; these are DDL triggers. As
you learned earlier, DDL statements are statements used to create and manage
server and database objects, such as tables, views, and even databases themselves.
However, most current DBMS products do not support DDL triggers.
Triggers are extremely flexible in what they can do. They can include nearly
any SQL language executable statement. They can be set up to run after the trig-
gering event, the statement that causes the trigger to fire. In the case of SQL
Server and some other DBMS products, you can have the trigger run in place of
the statement or statements in the triggering event. Triggers can be used to
enforce data integrity that cannot be enforced through normal constraints and
to set limits on what can be done to server and database objects. You can also
use triggers for security auditing, to block and track attempts to perform unau-
thorized actions.
You will typically limit trigger use to situations where you cannot enforce
your requirements with a constraint, such as a check or foreign key constraint.
That is because executing a trigger is more resource-intensive than enforcing a
constraint and has a bigger potential adverse affect on database performance.
5.2.2 Adjusting Factors Relating to Performance
Database performance can be adversely affected by a wide variety of factors.
Some factors are a result of application requirements. Normalization can some-
times be the source of your performance issues. Often, the most obvious culprit is
the need for joining. Joining is an elegant solution to the need for data integration,
