Trigger Not Firing

OK, I'm at a loss..it must be staring me right in the face.

I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire.

Any ideas?


CREATE TABLE [dbo].[PIF_MEP99] (
[PIFRecID] [int] NOT NULL ,
[MEPRecID] [int] NOT NULL ,
[PrimaryInd] [char] (1) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD
CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED
(
[PIFRecID],
[MEPRecID]
) ON [PRIMARY]
GO

INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd)
SELECT 1,1,'Y' UNION ALL
SELECT 2,1,'N' UNION ALL
SELECT 3,2,'N'
GO

CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99
FOR UPDATE, DELETE
AS
SET NOCOUNT ON

-- Rule 1: Prevent and MEP from having more than 1 PIF as Primary

IF Exists ( SELECT * FROM inserted i
INNER JOIN PIF_MEP99 p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y')
BEGIN
ROLLBACK TRAN
RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP'
END
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

BEGIN TRAN
INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y'
COMMIT TRAN
GO

SELECT * FROM PIF_MEP99
GO

SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i
INNER JOIN PIF_MEP p
ON i.MEPRecID = p.MEPRecID
AND i.PrimaryInd = 'Y'
AND p.PrimaryInd = 'Y'
GO

DROP TABLE PIF_MEP99
GO




Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam