trigger problem

From: David Boyd <davidb1588_at_hotmail.com>
Date: Fri, 15 Oct 2004 09:15:18 -0400
Message-ID: <BAY10-F41Zw5V6QcqnW0002452a@hotmail.com>

Hi List,

I created a trigger that inserts an entry into an audit table for each row AFTER INSERT OR UPDATE
OR DELETE on following table:

CREATE TABLE test
(

  col1 		 NUMBER(10)        NOT NULL,
  col2		 NUMBER(10)        NOT NULL,
  col3		 CHAR(8 BYTE)     NOT NULL,
  col4		 CHAR(8 BYTE)     NOT NULL,
  col5		 CHAR(8 BYTE)     NOT NULL,
  col6		 NUMBER(10)        NOT NULL,
  col7		 NUMBER(6,2),
  col8		 NUMBER(10),
  col9		 NUMBER(10),
  col10		 NUMBER(10),
  col11  	                 NUMBER(10),
  col12                       NUMBER(10),
  col13    	                NUMBER(10),
  col14                       NUMBER(10),
  col15                       NUMBER(5,2),
  col16                       VARCHAR2(4000 BYTE),
  col17                       VARCHAR2(4000 BYTE),
  col18                       NUMBER(2),
  col19                       CHAR(1 BYTE)

)

The trigger inserts an entry for updating on each field and for each field on deleting if the field is not null. So the trigger is 237 lines long. When I tried to delete a record from the test table that each field has a value, I got following error:

ORA-06502: PL/SQL: numeric or value error: number precision too large

I know the error is not related to the length of field. I can delete some records that a part of fields have value successfully. It seems to me the trigger buffer is too small. Some books indicate each trigger must be less than 32K in size. I did not have any issue to compile the trigger. Does 32K also include the contents when the trigger fires?

Thanks for any advice.

David

---

FREE pop-up blocking with the new MSN Toolbar – get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 15 2004 - 08:11:42 CDT