Oracle Objects initialized by a Tiigger?

Hi Everybody,

I've created an object in Oracle 8i.

create or replace type OBJID_TY as object(   nr number(12),
  ty number(7));
/

This works. Now I want to have a table that contains this object. This works too. Now I want the object to be initialized by a trigger.

CREATE OR REPLACE TRIGGER "WEB"."COMM_TAB_INS" BEFORE INSERT ON "COMM_TAB"
REFERENCING OLD AS old NEW AS new FOR EACH ROW begin
  :new.comm_id := objid_ty(1,4);
end;
/

comm_id is of the type OBJID_TY. When I try to insert into the COMM_TAB table, the following things happen. When we pass an object to the insert statement the values get passed and the insert works. When we do not provide an object in the insert statement the insert fails and SQL plus stops with an end-of-communication-channel error. I think it is caused by the fact that the :new.comm_id object does not have memory allocated. The memory allocated by the trigger seems to be freed before the statement is completed.

Does anybody have an idea to solve this problem. We would rather not provide every insert statement with the object.

Thanks

Harry Andree

mailto:Harry_Andree_at_mssm.edu Received on Mon Oct 18 1999 - 12:55:42 CDT