compile sql server online

Language: Layout:

--Title of this code
--Batches are separated by 'go'

select @@version as 'sql server version'

create table Patient( PatientId int, primaryDoctor_SSN int)
insert into Patient values (1,1),(2,1),(3,1),(4,1)

create table DOCTOR (Id int, SSN Int)
insert into DOCTOR values (1,1)

select * from Patient
           
IF OBJECT_ID ('assertion12','TR') IS NOT NULL
   DROP TRIGGER assertion12;
GO
-- This trigger prevents a row from being inserted in the Patients table
-- when current patient's primary doctor already has patients = 4 (max 4)

CREATE TRIGGER assertion12 ON Patient
AFTER INSERT
AS
IF EXISTS ( select count(P.primaryDoctor_SSN)
            from Patient P 
            JOIN inserted AS i 
            ON p.primaryDoctor_SSN = i.primaryDoctor_SSN 
            group by P.primaryDoctor_SSN
            having count(P.primaryDoctor_SSN) =4
          )
BEGIN
RAISERROR ('Primary Doctor Assigned to the Patient already has 4 patients.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;
GO

-- This statement attempts to insert a row into the Patient table
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.

INSERT INTO Patient values (5,1)
GO

View schema
Execution time: 0.02 sec, rows selected: 6, rows affected: 5, absolute service time: 0.2 sec