Triggers are most useful to enforce referential integrity, "Creating and Maintaining Tables," when you learned how to create tables. Referential integrity enforces rules used to ensure that data remains valid across multiple tables. Suppose a user entered the following command:

INPUT:

insert RECORDINGS values (12, "The Cross of Changes", 3, 1994)

ANALYSIS:

This perfectly valid SQL statement inserts a new record in the RECORDINGS table. However, a quick check of the ARTISTS table shows that there is no Artist_ID = 12. A user with INSERT privileges in the RECORDINGS table can completely destroy your referential integrity.

NOTE:Although many database systems can enforce referential integrity through the use of constraints in the CREATE TABLE statement, triggers provide a great deal more flexibility. Constraints return system error messages to the user, and (as you probably know by now) these error messages are not always helpful. On the other hand, triggers can print error messages, call other stored procedures, or try to rectify a problem if necessary.

Triggers and Transactions

The actions executed within a trigger are implicitly executed as part of a transaction. Here's the broad sequence of events:

A similar problem could exist for deletes from the RECORDINGS table. Suppose that when you delete an artist's only record from the RECORDINGS table, you also want to delete the artist from the ARTISTS table. If the records have already been deleted when the trigger is fired, how do you know which Artist_ID should be deleted? There are two methods to solve this problem:

• Delete all the artists from the ARTISTS table who no longer have any recordings in the RECORDINGS table.

• Examine the deleted logical table. Transact-SQL maintains two tables: DELETED and INSERTED. These tables, which maintain the most recent changes to the actual table, have the same structure as the table on which the trigger is created. Therefore, you could retrieve the artist IDs from the DELETED table and then delete these IDs from the ARTISTS table.

Example

INPUT:

create trigger delete_artists on RECORDINGS for delete as begin delete from ARTISTS where artist_id not in (select artist_id from RECORDINGS) end

Example

create trigger delete_artists on RECORDINGS for delete as begin delete ARTISTS from ARTISTS, deleted where ARTIST.artist_id = deleted.artist_id end

Restrictions on Using Triggers

You must observe the following restrictions when you use triggers:

• Triggers cannot be created on temporary tables.

• Triggers must be created on tables in the current database.

• Triggers cannot be created on views.

• When a table is dropped, all triggers associated with that table are automatically dropped with it.

Nested Triggers

Triggers can also be nested. Say that you have created a trigger to fire on a delete, for instance. If this trigger itself then deletes a record, the database server can be set to fire another trigger. This approach would, of course, result in a loop, ending only when all the records in the table were deleted (or some internal trigger conditions were met). Nesting behavior is not the default, however. The environment must be set to enable this type of functionality. Consult your database server's documentation for more information on this topic.

Testing SELECT Statements Before Implementation

If you are creating a report (using SQL*PLUS for an example) and the report is rather large, you may want to check spacing, columns, and titles before running the program and wasting a lot of time. A simple way of checking is to add where rownum < 3 to your SQL statement:

SYNTAX:

SQL> select * from employee_tbl where rownum < 5;

ANALYSIS:

You get the first four rows in the table from which you can check the spelling and spacing to see if it suits you. Otherwise, your report may return hundreds or thousands of rows before you discover a misspelling or incorrect spacing.