|
|
CS 422 - Database Programming I
Oracle 2 Day Developer Guide, Chapter 6: Using Triggers
Objectives:
This lesson discusses triggers, program code that runs when
specific events occur. Objectives important to this lesson:
- About Triggers
- Creating Triggers
- Changing Triggers
- Disabling and Enabling Triggers
- About Trigger Complilation and Dependencies
- Dropping Triggers
Concepts:
About Triggers
For the last couple of weeks, we have been looking at schema
objects in Oracle and at the creation of subprograms. This lesson is a
bit different. Triggers are SQL code objects that are part of a
database, and they are meant to run (fire, automatically execute) when
specific events occur. They are best used for functions that need the
same action every time a triggering
event takes place, whether and admin is present or not.
The text explains that a trigger:
- must be declared to be a trigger
- must have a unique
name (no other
trigger in the schema may have the same name)
- must have an event
that will cause it to run
- must have a BEGIN
statement
- must have an END
statement
- may be enabled or disabled (It is enabled by default
when it is created.)
- may also have restrictions
A trigger's structure looks like this:
TRIGGER trigger_name
triggering_event
[ trigger_restriction ]
BEGIN
triggered_action;
END;
A trigger in the disabled
state will not run. A trigger will run if three conditions are met:
- it is in the enabled
state
- and its trigger event occurs
- and its trigger restrictions are satisfied
(If the trigger has no restrictions,
this condition is automatically satisfied.)
As the text explains, a trigger is only run by the three
conditions above being met.
Creating Triggers
The text tells us to use the SQL Developer tool "Create
Trigger" or the
DDL command CREATE TRIGGER to begin creation of a trigger. The text
takes a sidestep on page 143 before it begins an example. It tells us
that one use of a trigger is to insert,
update, or delete a database record (row). When
it does so, the trigger will automatically create two temporary records
called OLD and NEW. The purpose of those records varies from one kind
of operation to another:
- INSERT - OLD receives no values when created, NEW receives
the values that will go in the new record
- UPDATE - OLD receives the values of the current record, NEW
receives the values that will go into the record
- DELETE - OLD receives the values of the current record, NEW
receives no values when created
This information is not used until the third tutorial in the
set that begins
on page 143 (6-3).
Create a trigger that logs
table changes
|
|
1. Create the
EVALUATIONS_LOG table:
CREATE TABLE EVALUATIONS_LOG ( log_date DATE, action VARCHAR2(50));
2. Create EVAL_CHANGE_TRIGGER:
CREATE OR REPLACE TRIGGER
EVAL_CHANGE_TRIGGER
AFTER INSERT OR UPDATE OR
DELETE ON EVALUATIONS
DECLARE
log_action
EVALUATIONS_LOG.action%TYPE;
BEGIN
IF INSERTING THEN
log_action := 'Insert';
ELSIF UPDATING THEN
log_action := 'Update';
ELSIF DELETING THEN
log_action := 'Delete';
ELSE
DBMS_OUTPUT.PUT_LINE('This code is not reachable.');
END IF;
INSERT INTO EVALUATIONS_LOG (log_date, action)
VALUES (SYSDATE,
log_action);
END;
|
This step creates a data
table to hold your information about changes to table EVALUATIONS,
which you can create in the tutorial on page 4-3.
This begins the trigger. Note that REPLACE would overwrite an existing
trigger by the same name. The trigger runs AFTER a change is made.
|
The
tutorials in this chapter use files and procedures created earlier in
chapter 4 (pages 4-3, 4-16, and 4-19). Read through them to see which
objects are necessary. If you have not made those objects, take a few
minutes to crate them first.
|
