-- Demonstrate multi-function (INSERT,UPDATE,DELETE) trigger, use of :new and :old,
-- and simple debugging using DBMS_OUTPUT.PUT_LINE
-- Copyright (c) 2004, Caleb.com

-- Note:  User will need EXECUTE privilege on DBMS_PIPE

-- Procedure to place one message into implicit pipe
CREATE OR REPLACE PROCEDURE pipe_message (str IN varchar2)
IS
   x   integer;
BEGIN
   DBMS_PIPE.PACK_MESSAGE( item=>str);
   x := DBMS_PIPE.SEND_MESSAGE( pipename=>'plsql_debug' );
END;
/


-- Procedure to extract one message from implicit pipe
CREATE OR REPLACE PROCEDURE pipe_listener IS
   x   integer;
   str varchar2(2000);
BEGIN
   x := dbms_pipe.receive_message(pipename=>'plsql_debug'); 
   IF x = 0 THEN 
      DBMS_PIPE.UNPACK_MESSAGE(str); 
      DBMS_OUTPUT.PUT_LINE(str);
   END IF; 
END;
/

-- Trigger will fire only for UPDATE of column SAL on table EMP
CREATE OR REPLACE TRIGGER emp_sal
BEFORE UPDATE OF sal ON emp
BEGIN
   pipe_message(ora_login_user || ' just tried to change a salary!');
   raise_application_error(-20008,'You just got caught, ' || ora_login_user || '!');
END;
/