Re: When do triggers fire vs data checks?

Stan,

if you can figure out how far the app is padding (use a trace_in_session or SQL*Net trace or if accessing via ODBC, ODBC trace) alter table and make the column wider.

Even better is to rtrim the value in the insert statement.

Mario

"Stan Brown" <stanb_at_panix.com> wrote in message news:9e0lm0$8md$1_at_panix3.panix.com...
> I am trying to upgared a third party application that logs data inot an
> Oracle 7.3.4.5 instance.
>
> The new application is getting ORA-1401 errors, and thanks to some very
> kind help from dbock_cdo_at_hotmail.com I was able to determine that the
> new application is pading a text filed with extra spaces on the end.
>
> So I put the following trigger in place to try to solve the problem:
>
>
>
> /*
> Create Triger For MWH_LOG */
> create or replace trigger MWH_LOG_trig
> before insert on MWH_LOG for each row
> declare
> l_date date;
> begin
> l_date := :new.dstamp+0;
> :new.dstamp := l_date;
> :new.brkr_id := rtrim(:new.brkr_id);
> :new.brkr_id := ltrim(:new.brkr_id);
> end;
> /
>
>
> Heres the table structure:
>
>
> SQL> describe mwh_log ;
> Name Null? Type
> ------------------------------- -------- ----
> DSTAMP DATE
> BRKR_ID CHAR(20)
> MWH_READ NUMBER
> BRKR_TYPE NUMBER(3)
>
> SQL> quit
>
> The filed BRKR_ID is the one thta's causing the 1401.
>
> However, I am still getting the 1401 error, even with this trugger in
> place!
>
> This doesn't seem to make sense to me. Are the sizes check _before_ the
> trigger fires?
>
> How can I work around this?
>
>
> bind 1: dty=3D1 mxl=3D128(79) mal=3D00 scl=3D00 pre=3D00 oacflg=3D01
> <=3D=3D BRKR_ID
> bfp=3D400a9a38 bln=3D79 avl=3D79 flg=3D05
> value=3D"11X
> "
Received on Thu May 17 2001 - 18:28:29 CDT