Re: [Fwd: Re: a trigger question]
Dear 'rkusenet',
Testing on IDS 7.31.UD5 (I must upgrade, and fix my 9.30 system after a
change of hard disks), I get the error I'd expect.
Which version of which database were you testing on which platform (mine is
- still - Solaris 7).
--
Jonathan Leffler (jleffler@us.ibm.com)
STSM, Informix Database Engineering, IBM Data Management
4100 Bohannon Drive, Menlo Park, CA 94025
Tel: +1 650-926-6921 Tie-Line: 630-6921
"I don't suffer from insanity; I enjoy every minute of it!"
----- Message from "rkusenet" <rkusenet@sympatico.ca> on Mon, 12 May 2003
09:08:22 -0400 -----
Subject: Re: a trigger
question
"Jonathan Leffler" <jleffler@earthlink.net> wrote
> >
> > CREATE PROCEDURE checkIt(arg1 INT , arg2 INT) RETURNING INT> > ...//checks if the tuple exists in tableB
> > ...//returns 1 if the check fails
>
> Fix it to raise an exception (probably -746) instead. Specify the
> error you want users to see as the string portion - you must also set
> the ISAM error, and there may be a suitable non-parameterized message
> you can use.
even with raise exception, on an non logged database, rows which are not
meant to be inserted due to business logic, will get inserted anyway
if re-entrant triggers are used.
Try the following code in an unlogged database. In this case business rule
dictates that if
the number is less than 100 reject it, else double it automatically.
==================================
create table table1
( fld1 integer not null
);
create procedure sp_table1 (p_in integer) returning integer ; if ( p_in < 100 ) then
raise exception -746,0,"Number less than 100" ;
end if ;
return (p_in *2 );
end procedure ;
create trigger tr_tableinsert on table1
referencing new as n
for each row ( execute procedure sp_table1(n.fld1) into fld1);
insert into table1 values(90);
insert into table1 values(165);
====================================
select * from table1 ;
fld1
90
330
2 row(s) retrieved.
As can be seem the first row got inserted despite the exception generated
by the stored procedure.
I don't think this is a bug. This is an expected behaviour in an unlogged
database.
And it will be a serious bug, if this happens in a logged database. I
checked the
same code in a logged database and it rejected the first row (90).
"Jonathan Leffler" <jleffler@us.ibm.com> wrote in message news:b9pgq9$9l4$1@terabinaries.xmission.com...
>
>
>
>
>
> Dear 'rkusenet',
>
> Testing on IDS 7.31.UD5 (I must upgrade, and fix my 9.30 system after a
> change of hard disks), I get the error I'd expect.
Please elaborate. Was it an unlogged database?
When I ran the script thru dbaccess, I too got the error message
"Number less than 100". But the row that generated that exception
still got inserted.
The version is 9.21.UC4 on Solaris 2.6
ravi