Triggerrrrrrrrrrrrrrrrrrrr

/* Commment : Ensure that the same Reasoncode is not used more than once within an ActivityType. Ex: ReasonCodeID=44; ReasonCode=570; Reason=Return to Natural Parent; linked to ActivityType = Trail Discharge ReasonCodeID=69; ReasonCode=570; Reason=Return to Natural Parent; linked to ActivityType = Discharge ReasonCodeID=101; ReasonCode=570; Reason=Return to Natural Parent (other); linked to ActivityType = Trail Discharge --> bad data*/
/* Begin Transaction Insert into ChildActivityReasonCode (ReasonCode, Reason, ReasonGroup, StatusID, ReasonShortDesc, BillingCodeID) Values (570, 'xxx22', 'xxx22', 17, 'xxx22', 1)
Insert into ChildActivityTypeToReasonCode Values (2, @@Identity) Commit Transaction
Begin Transaction Insert into ChildActivityReasonCode (ReasonCode, Reason, ReasonGroup, StatusID, ReasonShortDesc) Values (570, 'yyy', 'yyyy', 17, 'yyy') -- Return to Natural Parent
Insert into ChildActivityTypeToReasonCode Values (2, @@Identity) Commit Transaction
*/
CREATE TRIGGER dbo.trig_ReasonCode_AssertNoDuplicatesWithinActivity ON dbo.ChildActivityTypeToReasonCode
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @reasoncode varchar(50)
DECLARE @activityTypeID int
DECLARE @reasonCodeID int

SELECT @reasoncode = r.Reasoncode, @activityTypeID = i.ChildActivityTypeID, @reasonCodeID = i.ChildActivityReasonCodeIDFROM inserted i
INNER JOIN
dbo.ChildActivityReasonCode r on
i.ChildActivityReasoncodeID = r.ChildActivityReasoncodeID
WHERE r.FlagDeleted = 0
IF ( @reasoncode IS NOT NULL ) AND
EXISTS ( SELECT 'X' FROM dbo.ChildActivityTypeToReasonCode a2r
INNER JOIN
dbo.ChildActivityReasonCode r ON
a2r.ChildActivityReasoncodeID = r.ChildActivityReasoncodeID
WHERE
r.ChildActivityReasonCodeID <> @reasonCodeID
AND a2r.ChildActivityTypeID=@activityTypeID
a2r.ChildActivityTypeID=@activityTypeID
AND Coalesce(r.reasoncode, '-2') = @reasoncode AND r.FlagDeleted = 0 )
BEGIN
RAISERROR ('Duplicate ReasonCode not allowed within an Activity', 16, 1)
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END