Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Limiting list of values for a column from another table's column Expand / Collapse
Author
Message
Posted Saturday, October 26, 2013 5:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 17, Visits: 68
I want to limit the list of values for a column from another table's column with some conditions.
For that i tried to make RULE and added CHECK constraint on that column with a subquery .
But unfortunately sql does not allow subqueries neither in CHECK constraints nor in RULES defined on column.

I am using a stored procedure 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 the table.

Here is the create table statement for both of my tables :

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'.

I cannot handle this with foreign key constraint because NO_RANGE is a part of composite primary key and in RANGE table , thus i cant make a foreign key on NO_RANGE in ASSET_CLASS referencing a part of primary key from RANGE table.

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.
Post #1508656
Posted Monday, October 28, 2013 3:02 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, March 14, 2014 2:19 AM
Points: 2,820, Visits: 3,916
aeri_q (10/26/2013)
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 ..???
Magic table like Inserted and deleted handle these kind if data .

Try to use "instead of" trigger. it will help you.


-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #1508777
Posted Monday, October 28, 2013 6:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 17, Visits: 68
ok .. but how can i stop the insertion in instead of trigger if condition is not matched.
can u plz explain ???
Post #1508841
Posted Monday, October 28, 2013 7:02 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 11:49 AM
Points: 739, Visits: 2,470
CREATE TRIGGER CHECK_RANGE ON ASSET_CLASS
FOR INSERT, UPDATE
AS

IF NOT EXISTS(SELECT * FROM inserted i JOIN RANGE r ON i.NO_RANGE = r.NO_RANGE WHERE r.ACCNT_TYP = 'A')
BEGIN
RAISERROR('Invalid NO_RANGE', 16, -1);
ROLLBACK;
END;
GO





The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1508852
Posted Monday, October 28, 2013 8:51 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Yesterday @ 7:23 AM
Points: 17, Visits: 68
Thanks alot for your help.

Post #1508933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse