Problem with trigger

From: Meena Gunasekaran <meena_at_gwcom.com>
Date: 1997/09/05
Message-ID: <3410482F.68B@gwcom.com>#1/1

This is a multi-part message in MIME format.

--------------50B5308518E1

Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit

Hi,

        We are running Oracle 7.3.2.3 on Sun Solaris 2.5 We have a "Row level" trigger on a table which gets fired, if a particular record is inserted. The trigger then forms a msg and send it to a waiting daemon using the DBMS_PIPE package. Following is the problem we are facing:

        If the trigger is fired "after insert", the record gets inserted into the table, but it doesn't send the msg to the pipe. On the otherhand, if the trigger is fired "before insert", the msg is sent to the pipe, but the record doesn't get inserted into the table.

I've attached the files on the daemon and the trigger for your reference.

Any help on this is greatly appreciated....

Thanks,
Meena

--------------50B5308518E1

Content-Type: text/plain; charset=us-ascii; name="daemon.pc"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="daemon.pc"

To findout whether dbms_pipe package works:

SQL>set serveroutput on
declare
 s integer;
 msg varchar2(300);
 begin

 	s:=dbms_pipe.receive_message('testlog');
 	if (s!=0) then
 	 dbms_output.put_line('error in receive msg');
 	end if;
 	dbms_pipe.unpack_message(msg);
 	dbms_output.put_line('output:'||msg);

 end;
 .
SQL> /
--------------50B5308518E1

Content-Type: text/plain; charset=us-ascii; name="trig.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="trig.sql"

--Table definition

	create table mytest (
		name	varchar2(50),
		msg	varchar2(500),
		constraint pk_mytest primary key(name)
	);

• Trigger for status notifications

CREATE OR REPLACE TRIGGER testtrig
before INSERT ON mytest
for each row  

DECLARE
   v_status INTEGER;
   v_mess VARCHAR2(500);  

BEGIN
   IF (:new.msg = 'UP' OR :new.msg = 'DOWN' ) THEN

	v_mess := 'test_msg';
         DBMS_PIPE.PACK_MESSAGE('I');
 
         DBMS_PIPE.PACK_MESSAGE(v_mess);
 
         v_status := DBMS_PIPE.SEND_MESSAGE('testlog');
 
         IF v_status != 0 THEN
            RAISE_APPLICATION_ERROR(-20010, 'trigtest trigger' || 'couldnot send the message,status =  '||v_status);
         END IF;
      END IF;

END statusTrig;
/

--------------50B5308518E1--
Received on Fri Sep 05 1997 - 00:00:00 CDT