January 22, 2007 at 11:28 am
My Projects table has a Type (foreign key: "fk_type_id")
Projects also have multiple Sub Types....listed in a separate table (cols are fk_project_id and fk_subtype_id)
Each "sub type" belongs to a "type" (the sub type table also has a foreign "fk_type_id")
How to I ensure that all the sub types listed for my project belong to the same type as referred to by "fk_type_id"?
Thanks
January 22, 2007 at 12:27 pm
I don't think there is any built in procedure which does this. You need to write your own script or check for any third party tools for this.
MohammedU
Microsoft SQL Server MVP
February 21, 2007 at 6:48 am
here is my two senses to your problem.
it would help if i have your project table and type table definitions
---
-----
CREATE TABLE PROJECT
(
PROJECTID INT,
TYPEID INT
)
CREATE TABLE TYPE
(
TYPEID int,
TITLE
)
CREATE TABLE SUBTYPE
(
SUBTYPEID INT,
TYPEID
TITLE
)
CREATE TABLE PROJECT_SUBTYPE
(
PROJ_SUBTYPEID INT,
PROJECTID INT,
SUBTYPEID
)
-----
define trigger to get the misbehaving sub types
-----
CREATE TRIGGER trgSubTypeinType ON PROJECT_SUBTYPE
AFTER INSERT
AS
DECLARE @type int
declare @prjType int
select @type = A.TYPEID
from SUBTYPE A
WHERE A.SUBTYPEID = inserted.subtypeid
SELECT @prjType = prj.TYPEID
FROM project prj
where prj.projectid = inserted.projectid
IF @type @prjType
BEGIN
RAISERROR ('The Subtype must be the same type as the main project type', 16, 1)
ROLLBACK TRANSACTION
END
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply