From: mburns@sjm.com (Mark Burns)
Newsgroups: comp.database.oracle,comp.databases.oracle
Subject: Insert errors inside of triggers
Date: 7 Aug 2002 16:43:13 -0700
Organization: http://groups.google.com/
Lines: 44
Message-ID: <549bf180.0208071543.57e7ebad@posting.google.com>
NNTP-Posting-Host: 150.202.8.130
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1028763793 9977 127.0.0.1 (7 Aug 2002 23:43:13 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 7 Aug 2002 23:43:13 GMT


I am writing a trigger to populate an audit table and am getting the
following error when I attempt to do an insert.

ORA-01401: inserted value too large for column
ORA-06512: at "EIIS_TEST.CLINICAL_PARAMETERS_TRIGGER", line 17
ORA-04088: error during execution of trigger
'EIIS_TEST.CLINICAL_PARAMETERS_TRIGGER'

However, if I run either of the inserts from SQLplus there is no error
and the insert go fine. Why is it failing in the trigger?
Is there some weird formating going on? Because I should have plenty
of extra room in each column.

Here is the trigger code:
CREATE OR REPLACE TRIGGER "EIIS_TEST"."CLINICAL_PARAMETERS_TRIGGER"
AFTER INSERT OR DELETE OR UPDATE OF "CDT_ID", "CLINP_RANGE",
"CODE_NAME",
"DESCRIPTION", "EIISFRAG_ID", "ES_ID", "EVALUATION_ORDER",
"HAS_OFF_CONDITION", "INTERPRET_FORMULA", "NAME", "RESOLUTION",
"STATUS"
ON "EIIS_TEST"."CLINICAL_PARAMETERS" FOR EACH ROW
Declare
Master_ID number;

Begin
If updating then
/*Get a Master_ID number for this transaction from sequence*/
select audit_eiis_master_seq.nextval into Master_ID from dual;

insert into audit_eiis_master(AUDIT_MASTER_ID, AUD_DATE, ACTION,
AUD_USER ,TABLENAME)
   values(Master_ID, sysdate ,'UPDATE', user, 'CLINICAL_PARAMETERS');

insert into audit_cp(AUDIT_CP_ID, AUDIT_MASTER_ID, AUDIT_STATUS, NAME,
STATUS, EIISFRAG_ID, ES_ID, EVALUATION_ORDER, CDT_ID, CODE_NAME,
DESCRIPTION, CLINP_RANGE, RESOLUTION, INTERPRET_FORMULA,
HAS_OFF_CONDITION)  values(AUDIT_CP_SEQ.nextval,  'UPDATED',
:old.NAME, :old.STATUS, :old.EIISFRAG_ID, :old.ES_ID,
:old.EVALUATION_ORDER, :old.CDT_ID, :old.CODE_NAME, :old.DESCRIPTION,
:old.CLINP_RANGE, :old.RESOLUTION, :old.INTERPRET_FORMULA,
:old.HAS_OFF_CONDITION);

Any help would be greatly appreciated
Mark Burns