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:

  1. About Triggers
  2. Creating Triggers
  3. Changing Triggers
  4. Disabling and Enabling Triggers
  5. About Trigger Complilation and Dependencies
  6. 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.

Week 8 Assignment: Developer Guide, Trigger lesson

  • Carry out the tutorial assignments in chapter 6 of the Oracle Developer pdf. Make some notes about each section in the space provided on Blackboard for this assignment. A few screen captures of your outputs would be nice.