09-rsins.sql

来自「《Oracle8i PL/SQL程序设计》附源码」· SQL 代码 · 共 42 行

REM 09-RSINS.SQL
REM This file contains the LogRSInserts trigger from Chapter 9
REM 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].


CREATE OR REPLACE TRIGGER LogRSInserts
  BEFORE INSERT ON registered_students
  FOR EACH ROW
DECLARE
  v_Status     INTEGER;
BEGIN

  /* Pack the description into the buffer first. */
  DBMS_PIPE.PACK_MESSAGE('I');

  /* Pack the current user and the timestamp. */
  DBMS_PIPE.PACK_MESSAGE(user);
  DBMS_PIPE.PACK_MESSAGE(sysdate);

  /* Pack the new values. */
  DBMS_PIPE.PACK_MESSAGE(:new.student_ID);
  DBMS_PIPE.PACK_MESSAGE(:new.department);
  DBMS_PIPE.PACK_MESSAGE(:new.course);
  DBMS_PIPE.PACK_MESSAGE(:new.grade);

  /* Send the message over the 'RSInserts' pipe. */
  v_Status := DBMS_PIPE.SEND_MESSAGE('RSInserts');

  /* If the send is unsuccessful, raise an error so the change
     doesn't go through. */
  IF v_Status != 0 THEN
    RAISE_APPLICATION_ERROR(-20010, 'LogRSInserts trigger ' ||
      'couldn''t send the message, status = ' || v_Status);
  END IF;
  
END LogRSInserts;
/