Re: Insert errors inside of triggers

From: Daniel Morgan <dmorgan_at_exesolutions.com>
Date: Thu, 08 Aug 2002 15:34:41 GMT
Message-ID: <3D528F8B.6D220611_at_exesolutions.com>


Mark Burns wrote:

> 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

What the trigger is inserting is not what you are inserting manually. You may think they are the same but they are not. And triggers can not reformat or modify anything ... only your code can do that.

So I would build a duplicate of the table you are importing into with each field sized as something like VARCHAR2(100) or (1000) or whatever is so big the insert can not fail. Then modify your trigger to point to the new table. Then perform your manual insert.

You will see the difference.

If you don't ... I would like to have the table definitions and a sample record that does what you say is happening.

Daniel Morgan Received on Thu Aug 08 2002 - 17:34:41 CEST

Original text of this message