a trigger question
James wanted to block an insert into table A when a matching row exists in table B, but CHECK constraints can't contain SQL queries. His workaround (a trigger calling an SPL function that returns a value violating a CHECK constraint) reported "check constraint failed" yet the row was still inserted. Serge explained why queries aren't allowed in CHECK constraints; rkusenet noted the row-still-inserted behaviour is expected in an unlogged database. Madison suggested INSTEAD OF triggers on a view (9.4), unavailable on his 9.1. Jonathan Leffler gave the practical fix: have the SPL procedure RAISE EXCEPTION (e.g. -746) with your own message rather than relying on the CHECK constraint.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi,
i can't seem to find a solution to the following problem:
Inserting a tuple in table A is only allowed if a certain tuple
does NOT exist in a table B.
As far as i know, no sql queries are allowed in CHECK constraints (how
come?).
So i thought about using triggers and SPL. But i can't seem to get it
to work properly.This what i do:
CREATE TABLE tableA{
field1 INT,
field2 INT,
field3 INT,
CHECK(field3 = 0)
);
CREATE PROCEDURE checkIt(arg1 INT , arg2 INT) RETURNING INT...//checks if the tuple exists in tableB
...//returns 1 if the check fails
END PROCEDURE
CREATE TRIGGER insTrigINSERT ON tableA
REFERENCING NEW AS new
FOR EACH ROW(EXECUTE FUNCTION CheckIt(new.field1 , new.field2) INTO
field3);
When i try to insert a tuple that should not be inserted because it
does not confirm to the condition, i get an "check constraint failed"
error but the tuple is inserted anyway.
Any help is welcome,
James
Hi, >As far as i know, no sql queries are allowed in CHECK constraints (how come?). To support SQL queries in CHECK constraints the DBMS needs to track all changes on the tables underlying the queries to ensure they don't violate the constraints (just like RI). An RI constraint is a very simplified version of a check constraint with query. The general case is a lot harder to test efficiently because murphy knows how that query looks like. So at the end of the day the DBMS would have to re-execute the queries for all involved tables. So I take it, if Informix (or any other DBMS) would support it, it would be a feature to be avoided. Note, materialized query tables (aka materialized views) go part of the way. Cheers Serge -- Serge Rielau DB2 UDB SQL Compiler Development IBM Software Lab, Toronto Visit DB2 Developer Domain at http://www7b.software.ibm.com/dmdd/
"james van hees" <jamesvanhees@altavista.com> wrote > When i try to insert a tuple that should not be inserted because it > does not confirm to the condition, i get an "check constraint failed" > error but the tuple is inserted anyway. this will happen only when your database is not a logged one and is an expected behaviour in an unlogged database. Ravi
Serge Rielau <srielau@ca.eye-bee-em.com> wrote in message news:<3EBDB5B3.40705@ca.eye-bee-em.com>... > Hi, > > >As far as i know, no sql queries are allowed in CHECK constraints (how > come?). > To support SQL queries in CHECK constraints the DBMS needs to track all > changes on the tables underlying the queries to ensure they don't > violate the constraints (just like RI). > An RI constraint is a very simplified version of a check constraint with > query. > The general case is a lot harder to test efficiently because murphy > knows how that query looks like. > So at the end of the day the DBMS would have to re-execute the queries > for all involved tables. > So I take it, if Informix (or any other DBMS) would support it, it would > be a feature to be avoided. > > Note, materialized query tables (aka materialized views) go part of the way. > > Cheers > Serge hey, thanks for your reply, but i'm confused about how i can solve my problem in informix. I don't know much about materialized views but from what i have read on the net, it isn't what i'm looking for. Or am i wrong? I just need a conditional insert, where the condition is an sql query. Is this in any way possible in informix? greetz, james
Yes --- You can use instead-of triggers on a view. (9.4) "james van hees" <jamesvanhees@altavista.com> wrote in message news:7637fe52.0305111248.785d6bb8@posting.google.com... > Serge Rielau <srielau@ca.eye-bee-em.com> wrote in message news:<3EBDB5B3.40705@ca.eye-bee-em.com>... > > Hi, > > > > >As far as i know, no sql queries are allowed in CHECK constraints (how > > come?). > > To support SQL queries in CHECK constraints the DBMS needs to track all > > changes on the tables underlying the queries to ensure they don't > > violate the constraints (just like RI). > > An RI constraint is a very simplified version of a check constraint with > > query. > > The general case is a lot harder to test efficiently because murphy > > knows how that query looks like. > > So at the end of the day the DBMS would have to re-execute the queries > > for all involved tables. > > So I take it, if Informix (or any other DBMS) would support it, it would > > be a feature to be avoided. > > > > Note, materialized query tables (aka materialized views) go part of the way. > > > > Cheers > > Serge > > hey, > > thanks for your reply, but i'm confused about how i can solve my > problem in informix. I don't know much about materialized views but > from what i have read on the net, it isn't what i'm looking for. Or am > i wrong? > I just need a conditional insert, where the condition is an sql query. > Is this in any way possible in informix? > > greetz, > james
> Yes --- > > You can use instead-of triggers on a view. (9.4) > > > I'm using Universal Server v9.1 wich (apparantly) does not support instead-of triggers.
james van hees wrote:
> i can't seem to find a solution to the following problem:
> Inserting a tuple in table A is only allowed if a certain tuple
> does NOT exist in a table B.
>
> As far as i know, no sql queries are allowed in CHECK constraints (how
> come?).
Serge answered this question nicely.
> So i thought about using triggers and SPL. But i can't seem to get it
> to work properly.This what i do:
>
> CREATE TABLE tableA{
> field1 INT,
> field2 INT,
> field3 INT,
> CHECK(field3 = 0)
> );>
> 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.
> END PROCEDURE
>
> CREATE TRIGGER insTrig> INSERT ON tableA
> REFERENCING NEW AS new
> FOR EACH ROW(EXECUTE FUNCTION CheckIt(new.field1 , new.field2) INTO
> field3);
>
>
> When i try to insert a tuple that should not be inserted because it
> does not confirm to the condition, i get an "check constraint failed"
> error but the tuple is inserted anyway.
>
> Any help is welcome,
> James
--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/