05-trig.sql

《Oracle8i PL/SQL程序设计》附源码

REM 05-TRIG.SQL
REM This file contains the trigger examples
REM used in Chapter 5 of "Oracle PL/SQL Programming".

REM This is version 1.0 of this file, updated 2/18/96.
REM Comments and questions should go to Scott Urman at
REM [e-mail].


REM *** Chapter 5: Creating a Trigger ***
CREATE OR REPLACE TRIGGER UpdateMajorStats
  /* Keeps the major_stats table up-to-date with changes made
     to the students table. */
  AFTER INSERT OR DELETE OR UPDATE ON students
DECLARE
  CURSOR c_Statistics IS
    SELECT major, COUNT(*) total_students,
           SUM(current_credits) total_credits
      FROM students
      GROUP BY major;
BEGIN
  /* Loop through each major. Attempt to update the statistics
     in major_stats corresponding to this major. If the row
     doesn't exist, create it. */
  FOR v_StatsRecord in c_Statistics LOOP
    UPDATE major_stats
      SET total_credits = v_StatsRecord.total_credits,
          total_students = v_StatsRecord.total_students
      WHERE major = v_StatsRecord.major;
    /* Check to see if the row exists. */
    IF SQL%NOTFOUND THEN
      INSERT INTO major_stats (major, total_credits, total_students)
        VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,
                v_StatsRecord.total_students);
    END IF;
  END LOOP;
END UpdateMajorStats;
/

REM *** Chapter 5: :old and :new Pseudo-records, Example 1 ***
CREATE OR REPLACE TRIGGER TempDelete
BEFORE DELETE ON temp_table
FOR EACH ROW
DECLARE
  v_TempRec temp_table%ROWTYPE;
BEGIN
  /* This is not a legal assignment, since :old is not truly
     a record. */
  v_TempRec := :old;

  /* We can accomplish the same thing, however, by assigning
    the fields individually. */
  v_TempRec.char_col := :old.char_col;
  v_TempRec.num_col := :old.num_col;
END TempDelete;
/

REM *** Chapter 5: :old and :new Pseudo-records, Example 2 ***
CREATE OR REPLACE TRIGGER GenerateStudentID
  BEFORE INSERT OR UPDATE ON students
  FOR EACH ROW
BEGIN
  /* Fill in the ID field of students with the next value from
     student_sequence. Since ID is a column in students, :new.ID
     is a valid reference. */
  SELECT student_sequence.nextval
    INTO :new.ID
    FROM dual;
END GenerateStudentID;
/

REM *** Chapter 5: INSERTING, DELETING and UPDATING Predicates ***
CREATE OR REPLACE TRIGGER LogRSChanges
  BEFORE INSERT OR DELETE OR UPDATE ON registered_students
  FOR EACH ROW
DECLARE
  v_ChangeType CHAR(1);
BEGIN
  /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
  IF INSERTING THEN
    v_ChangeType := 'I';
  ELSIF UPDATING THEN
    v_ChangeType := 'U';
  ELSE
    v_ChangeType := 'D';
  END IF;

  /* Record all the changes made to registered_students in
     RS_audit. Use SYSDATE to generate the timestamp, and
     USER to return the userid of the current user. */
  INSERT INTO RS_audit
    (change_type, changed_by, timestamp,
     old_student_id, old_department, old_course, old_grade, 
     new_student_id, new_department, new_course, new_grade)
  VALUES
    (v_ChangeType, USER, SYSDATE,
     :old.student_id, :old.department, :old.course, :old.grade,
     :new.student_id, :new.department, :new.course, :new.grade);
END LogRSChanges;
/