Re: using systables in table constraint
sjhillman@tasc.com (Seth J. Hillman) writes:
>In my database, I have a table (foo) which has a
>column (footable) that stores table names. The tables
>listed in column footable are used as lookup tables by my
>application. Is there a way to place a constraint on
>on footable such that the table name must be a valid
>table?
Create a trigger on the insert to foo and one on the update of foo
that each passes new.fooname to a (the same) stored procedure that does:
CREATE PROCEDURE check_tabname (p_tabname CHAR(18))
DEFINE p_check SMALLINT DEFAULT NULL; SELECT 1 INTO p_check FROM systables WHERE tabname = p_tabname;
IF p_check IS NULL THEN
RAISE EXCEPTION -746, 0, "Table name " || p_tabname || " does not exist";
END IF;
END PROCEDURE;
If your application gets an SQL error -746 (this is the error code given to
triggered actions for free-form application-generated messages), you'll know
that the inserted or updated table name was not contained in systables.
___ ___ Senior Consultant
/ ) __ . __/ /_ ) _ _ __ Informix Software Inc. (303) 850-0210
_/__/ (_(_ (/ / (_(_ _/__) (-' ~/ '(_- 5299 DTC Blvd #740 Englewood CO 80111