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.|/ ////////| +----------------------+-----------------------------------+-----------+