Yes you understood well my question.

I am already using a stored procedure to for insertions in my table, but the procedure is dynamically generated from the application(Desktop Application) , and is used for insertions in many other tables as well . That is why , i cannot handle the necessary checks in the procedure. I want some database level check , which could raise an error if an out of list value is tried to be inserted in table1's col2.

Here is the create table statement for both of my tables , so that u could understand the stucture:

CREATE TABLE RANGE

(

SYST varchar(3) NOT NULL,

ACCNT_TYP varchar(1) NOT NULL,

NO_RANGE varchar(2) NOT NULL,

FROM_NO varchar(10) NOT NULL,

TO_NO varchar(10) NOT NULL,

CURRENT_NO varchar(10) NOT NULL,

PRIMARY KEY (SYST, ACCNT_TYP, NO_RANGE)

)

CREATE TABLE ASSET_CLASS

(

SYST varchar(3) NOT NULL,

ASSETCL varchar(5) NOT NULL,

ACCNTDTR varchar(5) NULL,

NO_RANGE varchar(2) ,

SCRN_LO varchar(5),

PRIMARY KEY (SYST, ASSETCL)

)

What i want, is a check like this, to be applied on NO_RANGE column of ASSET_CLASS at the time of insertion:

CHECK (LIST IN (SELECT NO_RANGE FROM RANGE WHERE ACCNT_TYP = 'A')

thus, No_RANGE in ASSET_CLASS can hold only those NO_RANGES which exist in RANGE table and have an ACCNT_TYP = 'A'.

Please help me to achieve this .. ??

If i write a trigger for INSERT ON ASSET_CLASS, i dunt know how to put a check on the value which is tried to be inserted , I mean how will i access the value which is tried to be inserted in NO_RANGE column , to perform the check ..???

:S I dunt know how to do this... Any help would be greatly appreciated.