Posted by --CELKO-- on 12/01/06 03:32

You have three possible actions which should be in separate procedures

CREATE PROCEDURE DelAppointment (@myappt_id INTEGER)
DELETE FROM Appointments -- meeting cancelled
WHERE appt_id = @my_appt_id;

CREATE PROCEDURE DelAttendee(@my_attendee_id INTEGER)
DELETE FROM Attendees -- employee gone
WHERE attendee_id = @my_attendee_id;

CREATE PROCEDURE DelAttendance
(@my_attendee_id INTEGER , @myappt_id INTEGER)
BEGIN
DELETE FROM Attendance -- employee removed from meeting
WHERE appt_id = @my_appt_id
AND attendee_id = @my_attendee_id;
-- if the was the next to last guy, cancel the meeting
DELETE FROM Appointments
WHERE appt_id
IN (SELECT A1.appt_id
FROM Attendance AS A1
GROUP BY A1.appt_id
HAVING COUNT(*) <= 1);
END;

CREATE PROCEDURE
(@my_attendee_id INTEGER , @myappt_id INTEGER)

Watch your error handling and transaction level in the last procedure.

[Back to original message]