Use DBMS_PIPE.PACK_MESSAGE in a trigger : DBMS_PIPE « System Packages « Oracle PL / SQL






Use DBMS_PIPE.PACK_MESSAGE in a trigger

    

CREATE TABLE myStudent (
  student_id NUMBER(5) NOT NULL,
  department CHAR(3)   NOT NULL,
  course     NUMBER(3) NOT NULL,
  grade      CHAR(1)
  );

CREATE OR REPLACE TRIGGER LogRSInserts
  BEFORE INSERT ON myStudent
  FOR EACH ROW
DECLARE
  v_Status     INTEGER;
BEGIN
  DBMS_PIPE.PACK_MESSAGE('I');

  DBMS_PIPE.PACK_MESSAGE(user);
  DBMS_PIPE.PACK_MESSAGE(sysdate);

  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);

  v_Status := DBMS_PIPE.SEND_MESSAGE('RSInserts');

  IF v_Status != 0 THEN
    RAISE_APPLICATION_ERROR(-20010, 'LogRSInserts trigger ' || 'couldn''t send the message, status = ' || v_Status);
  END IF;
  
END LogRSInserts;
/


INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000, 'CS', 102, 'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10002, 'CS', 102, 'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10003, 'CS', 102, 'C');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000, 'HIS', 101, 'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10001, 'HIS', 101, 'B');

drop table myStudent;


--

   
    
    
  








Related examples in the same category

1.DBMS_PIPE.PACK_MESSAGE
2.DBMS_PIPE.UNPACK_MESSAGE
3.dbms_pipe.remove_pipe
4.Use DBMS_PIPE package to receive a message.
5.Use DBMS_PIPE package to send a message.
6.Define an anonymous block to populate the local private pipe.
7.An anonymous block program to create a pipe.
8.An anonymous block program to delete a pipe
9.An Oracle9i Pipelined Table Function
10.Run a DBMS_PIPE.RECEIVE_MESSAGE call to empty the local buffer
11.This script deletes a pipe if it exists in the context of the current session, then recreates it.
12.This script unpacks the local buffer.