/*Has been edited by me*/
GO
/****** Object:  Table [dbo].[myQuota]    Script Date: 05/21/2012 00:03:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[myQuota](
	[quotaIdx] [int] NOT NULL,
	[mQuota] [int] NOT NULL,
	[fQuota] [int] NOT NULL,
 CONSTRAINT [PK_myQuota] PRIMARY KEY CLUSTERED 
(
	[quotaIdx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[myQuota] ([quotaIdx], [mQuota], [fQuota]) VALUES (1, 1, 1)
INSERT [dbo].[myQuota] ([quotaIdx], [mQuota], [fQuota]) VALUES (2, 10, 1)
/****** Object:  Table [dbo].[myTrigger]    Script Date: 05/21/2012 00:03:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[myTrigger](
	[idx] [int] NOT NULL,
	[quotaIdx] [int] NULL,
	[nme] [varchar](50) NOT NULL,
	[gender] [char](1) NULL,
	[acceptStat] [bit] NULL,
	[lastAcceptDte] [date] NULL,
 CONSTRAINT [PK_myTrigger] PRIMARY KEY CLUSTERED 
(
	[idx] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[myTrigger] ([idx], [quotaIdx], [nme], [gender], [acceptStat], [lastAcceptDte]) VALUES (1, 1, N'Captain America', N'm', 1, CAST(0xB2350B00 AS Date))
INSERT [dbo].[myTrigger] ([idx], [quotaIdx], [nme], [gender], [acceptStat], [lastAcceptDte]) VALUES (2, 1, N'Black Window', N'f', 1, CAST(0xB7350B00 AS Date))
INSERT [dbo].[myTrigger] ([idx], [quotaIdx], [nme], [gender], [acceptStat], [lastAcceptDte]) VALUES (3, NULL, N'Iron Man', N'm', NULL, CAST(0xBE350B00 AS Date))
/****** Object:  ForeignKey [FK_myTrigger_myQuota]    Script Date: 05/21/2012 00:03:09 ******/
ALTER TABLE [dbo].[myTrigger]  WITH CHECK ADD  CONSTRAINT [FK_myTrigger_myQuota] FOREIGN KEY([quotaIdx])
REFERENCES [dbo].[myQuota] ([quotaIdx])
GO
ALTER TABLE [dbo].[myTrigger] CHECK CONSTRAINT [FK_myTrigger_myQuota]
GO

update myTrigger set quotaIdx=1, acceptStat='true' where idx=3 

update myTrigger set quotaIdx=1, acceptStat='true' where idx=3

quote:

---

Originally posted by wkm1925

Sir Visakh,

I've changed my tables and data ~ see my above post

Looks like CHECK constraint more accurate. How the CHECK constraint looks like?

---

CREATE FUNCTION CheckIdxQuota
(
@Idx int,
@Gender char(1)
)
RETURNS bit 
AS
BEGIN
DECLARE @Ret bit,@Cnt int
SET @Ret=1

SELECT @Cnt = COUNT(*)
FROM myTrigger
WHERE [idx] = @Idx
AND [gender] = @Gender


SELECT @Ret=CASE WHEN CASE @Gender WHEN 'm' THEN [mQuota] ELSE [fQuota] END < @Cnt THEN 0 ELSE 1 END
FROM myQuota
WHERE [quotaIdx] = @Idx

RETURN (@Ret)
END


then use it in check constraint as

ALTER TABLE [dbo].[myTrigger] ADD CONSTRAINT chk_GenderQuota CHECK (dbo.CheckIdxQuota([idx],[gender]) = 1)