Re: a trigger question

james van hees wrote: > 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?). A CHECK constraint is a simple domain check. Use constraints for simple data validation. Use Triggers & Stored Procedures for complicated data validation. > 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 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. First of all, you need to be using a database with transaction logging. Second, the way you have tried it is a nice idea, but never going to work. The way to generate an error within a stored procedure is to use RAISE EXCEPTION. If you are within a transaction, then both the SP and the trigger will be stopped. You then just need to check for the error in your application and then ROLLBACK WORK. You only need the key fields passed to the SP, so I presume you have a composite key. So your SP should look something like this: CREATE PROCEDURE CheckIt(arg1 INT, arg2 INT) IF EXISTS ( SELECT field1, field2 FROM tableB WHERE field1 = arg1 AND field2 = arg2 ) THEN RAISE EXCEPTION -100, 0, ""; END IF; END PROCEDURE Substitute a valid error number instead of -100, or use error -745 (I think) which is a generic trigger failure error. Then your trigger can look something like: CREATE TRIGGER insTrigINSERT ON tableA REFERENCING NEW AS new FOR EACH ROW(EXECUTE PROCEDURE CheckIt(new.field1, new.field2)) I presume that you can then remove column field3. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+

Mark D. Stock wrote in message ... > >james van hees wrote: >> 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?). > >A CHECK constraint is a simple domain check. Use constraints for simple >data validation. Use Triggers & Stored Procedures for complicated data >validation. > >> 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 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. > >First of all, you need to be using a database with transaction logging. > >Second, the way you have tried it is a nice idea, but never going to >work. The way to generate an error within a stored procedure is to use >RAISE EXCEPTION. If you are within a transaction, then both the SP and >the trigger will be stopped. You then just need to check for the error >in your application and then ROLLBACK WORK. > >You only need the key fields passed to the SP, so I presume you have a >composite key. So your SP should look something like this: > >CREATE PROCEDURE CheckIt(arg1 INT, arg2 INT)> > IF EXISTS > ( > SELECT field1, field2 > FROM tableB > WHERE field1 = arg1 > AND field2 = arg2 > ) > THEN > RAISE EXCEPTION -100, 0, "";> END IF; > >END PROCEDURE > >Substitute a valid error number instead of -100, or use error -745 (I >think) which is a generic trigger failure error. -746 is the generic error. Why not: RAISE EXCEPTION -530, 0, "Key already exists in tableB"; ? dbaccess (at least) then gives: 530: Check constraint (Key already exists in tableB) failed. > >Then your trigger can look something like: > >CREATE TRIGGER insTrig>INSERT ON tableA >REFERENCING NEW AS new >FOR EACH ROW(EXECUTE PROCEDURE CheckIt(new.field1, new.field2)) > >I presume that you can then remove column field3. Another way, which doesn't need a trigger/procedure ... -- create a table to hold the "master" keys ... CREATE TABLE tableX ( field1 INTEGER NOT NULL, field2 INTEGER NOT NULL, t CHAR(1) NOT NULL, CHECK (t in ("A", "B")), PRIMARY KEY (field1, field2, t), UNIQUE (field1, field2) ); -- ... and two (or more) "proper" tables ... CREATE TABLE tableA ( field1 INTEGER NOT NULL, field2 INTEGER NOT NULL, t CHAR(1) DEFAULT "A" NOT NULL, CHECK (t="A"), PRIMARY KEY(field1, field2), FOREIGN KEY(field1, field2, t) REFERENCES tableX ); create table tableB ( field1 INTEGER NOT NULL, field2 INTEGER NOT NULL, t CHAR(1) default "B" NOT NULL, CHECK (t="B"), PRIMARY KEY( field1, field2), FOREIGN KEY(field1, field2, t) REFERENCES tableX ); -- You must insert the key into tableX first ... INSERT INTO tableX VALUES (1, 2, "A");-- then, into tableA ... INSERT INTO tableA(field1,field2) VALUES (1,2); -- ditto ... INSERT INTO tableX VALUES (3, 4, "B"); INSERT INTO tableB(field1,field2) VALUES (3,4); -- can't allow either of these, because {3,4} already exists for tableB ... INSERT INTO tableX VALUES (3, 4, "A"); INSERT INTO tableA(field1,field2) VALUES (3,4); -- can't do this, because key doesn't exists in tableX ... INSERT INTO tableA(f1,f2) VALUES (5,6); > >Cheers, >-- >Mark. > >+----------------------------------------------------------+-----------+ >| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| >| Mydas Solutions Ltd http://MydasSolutions.com |///// / //| >| +-----------------------------------+//// / ///| >| |We value your comments, which have |/// / ////| >| |been recorded and automatically |// / /////| >| |emailed back to us for our records.|/ ////////| >+----------------------+-----------------------------------+-----------+ >