1) First insert the following data in your database

insert into department values ('CSE', 'Voit Dr');

INSERT INTO FACULTY VALUES (101, 'PROF', NULL, 'LAST-1', 'PROFESSOR');
INSERT INTO FACULTY VALUES (102, 'ASSOC', NULL, 'LAST-2', 'ASSOCIATE PROFESSOR');
INSERT INTO FACULTY VALUES (103, 'LECT', NULL, 'LAST-3', 'LECTURER');
INSERT INTO FACULTY VALUES (1000, 'ALIN', NULL, 'DEUTSCH', 'ASSISSTANT PROFESSOR');

INSERT INTO STUDENT VALUES (100, 1, 'STU-1', NULL, 'LAST-01', 'FOREIGN');
INSERT INTO STUDENT VALUES (200, 2, 'STU-2', NULL, 'LAST-02', 'NON-CA');


insert into grad values(100, 'MS', 'CSE');
insert into grad values(200, 'PHD', 'CSE');

insert into grad_commitee values (100, 101);
insert into grad_commitee values (100, 102);
insert into grad_commitee values (100, 103);
insert into grad_commitee values (100, 1000);
insert into grad_commitee values (200, 101);
insert into grad_commitee values (200, 102);
insert into grad_commitee values (200, 103);
insert into grad_commitee values (200, 1000);

Now, there are 4 faculty in thesis commitee of student 100 and 200


2) Then create the following trigger with grad_commitee

CREATE TRIGGER MFT on grad_commitee
FOR DELETE
AS
if (3 > all(select count(faculty) from grad_commitee group by grad))
begin
raiserror('There should be atleast 3 faculty in a thesis commitee', 10, 10)
rollback transaction
end



3) Now we delete 1 faculty from the thesis commitee of both the students

delete from grad_commitee where faculty=1000;

   The trigger will allow this as there are still 3 faculty in each thesis commitee

3) Now we delete another faculty from the thesis commitee of both the students

delete from grad_commitee where faculty=101;

   The trigger will not allow this because if this delete is executed than there will be
   only 2 faculty in each thesis commitee

4) Now we try and delete all the contents of this table

   delete from grad_commitee

   The trigger will not allow this.