I wrote a couple SQL triggers for handling Primary Contacts because the multiple Primary Contacts feature was causing multiple rows in our reports, and because I felt that the words "Multiple" and "Primary" should not be used in the same sentence.
The first trigger removes the IsPrimary value from the original Primary Contact after a new Primary Contact is added. The second trigger prevents someone from removing the only Primary Contact, which was necessary after giving our technicians access to remove Contacts from the All Contacts web part.
USE [ProcessManager]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- This trigger prevents a technician from adding more than one Primary Contact
CREATE TRIGGER [dbo].[trg_rpc_insert_audit_primarycontact]
ON [dbo].[ReportProcessContact]
AFTER INSERT
AS
UPDATE dbo.ReportProcessContact
SET ReportProcessContact.IsPrimary = 0
FROM dbo.ReportProcessContact AS RPC
INNER JOIN inserted AS i
ON RPC.SessionID = i.SessionID
WHERE RPC.ReferenceID <> i.ReferenceID
AND RPC.IsPrimary = 1
AND i.IsPrimary = 1
GO
-- Prevents a technician from deleting the only Primary Contact
CREATE TRIGGER [dbo].[trg_rpc_delete_audit_primarycontact]
ON [dbo].[ReportProcessContact]
AFTER DELETE
AS
INSERT INTO ReportProcessContact
SELECT *
FROM deleted
WHERE IsPrimary = 1;
GO