New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Functions
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Cursors
Stored Procedures
Triggers
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Designing and Using Triggers


    A trigger is essentially a special type of stored procedure that can be executed in response to one of three conditions:

An UPDATE

An INSERT

A DELETE

The Transact-SQL syntax to create a trigger looks like this:


SYNTAX:

create trigger trigger_name on table_name for {insert, update, delete}
as SQL_Statements

The Oracle7 SQL syntax used to create a trigger follows.

SYNTAX:

CREATE [OR REPLACE] TRIGGER [schema.]trigger_name {BEFORE | AFTER}
{DELETE | INSERT | UPDATE [OF column[, column]...]}
[OR {DELETE | INSERT | UPDATE [OF column [, column] ...]}]...
ON [schema.]table
[[REFERENCING { OLD [AS] old [NEW [AS] new] | NEW [AS] new [OLD [AS] old]}]
FOR EACH ROW [WHEN (condition)] ] pl/sql statements...

    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:

1. A BEGIN TRANSACTION statement is implicitly issued (for tables with triggers).

2. The insert, update, or delete operation occurs.

3. The trigger is called and its statements are executed.

4. The trigger either rolls back the transaction or the transaction is implicitly committed.

Example

    This example illustrates the solution to the RECORDINGS table update problem mentioned earlier.


INPUT:

create trigger check_artists on RECORDINGS
for insert, update as
if not exists (select * from ARTISTS, RECORDINGS
where ARTISTS.artist_id = RECORDINGS.artist_id)
begin print "Illegal Artist_ID!"
rollback transaction
end

ANALYSIS:

    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.

Using SELECT Commands with UPDATE and DELETE


Here are some complex SQL statements using UPDATE and DELETE:

INPUT:

SQL> UPPDATE EMPLOYEE_TBL SET LAST_NAME = 'SMITH'
WHERE EXISTS (SELECT EMPLOYEE_ID FROM PAYROLL_TBL WHERE EMPLOYEE_ID = 2);

ANALYSIS:

    The EMPLOYEE table had an incorrect employee name. We updated the EMPLOYEE table only if the payroll table had the correct ID.

INPUT/OUTPUT:

SQL> UPDATE EMPLOYEE_TABLE SET HOURLY_PAY = 'HOURLY_PAY * 1.1
WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID FROM PAYROLL_TBL WHERE EMPLOYEE_ID = '222222222');

ANALYSIS:

We increased the employee's hourly rate by 10 percent.

INPUT/OUTPUT:

SQL> DELETE FROM EMPLOYEE_TBL
WHERE EMPLOYEE_ID = (SELECT EMPLOYEE_ID FROM PAYROLL_TBL WHERE EMPLOYEE_ID = '222222222';

ANALYSIS:

Here we deleted an employee with the ID of 222222222.

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.