This trigger sends messages over a pipe to record inserts into myStudent. : Create Trigger « Trigger « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Trigger
  3. Create Trigger

This trigger sends messages over a pipe to record inserts into myStudent.

   

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




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

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

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

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10004, 'HIS', 101, 'C');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10005, 'HIS', 101, 'C');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10006, 'HIS', 101, 'E');

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

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

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10009, 'HIS', 101, 'D');

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

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10008, 'NUT', 307, 'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10010, 'NUT', 307, 'A');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10009, 'MUS', 410, 'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10006, 'MUS', 410, 'E');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10011, 'MUS', 410, 'B');

INSERT INTO myStudent (student_id, department, course, grade)
                         VALUES (10000, 'MUS', 410, 'B');

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;
/
show errors

drop table myStudent;








Related examples in the same category

1.create or replace trigger
2.Oracle's syntax for creating a trigger based on two tables
3.Trigger on each row
4.Use Sequence in a trigger
5.Empty trigger(before insert or update or delete)
6.Create tigger on wrapper table
7.Cascade inserts into myStudent into session and lecturer.
8.Trigger is autonomous and hence the changes will be logged even if the original transaction rolls back.
9.Trigger Which Modifies a Mutating Table
10.Creating a Trigger with cursor inside
11.Autonumbering Trigger
12.Use RAISE_APPLICATION_ERROR in a trigger
13.Show errors for a trigger
14.Submit job from a trigger
15.Use sysdate and user function in a trigger