Trigger question

[lespaul]

If I'm creating this as a before insert trigger (ps - I'm new at triggers, feel free to point out better ways to do what I'm doing. I cobbled this together from looking at examples in the Redbook):

/* Creating SQL trigger LANDREWS.CHECKATTENDANCE */
CREATE TRIGGER LANDREWS.CHECKATTENDANCE 
BEFORE INSERT ON LANDREWS.HRPEMPLCRSSCH 
REFERENCING NEW N 
FOR EACH ROW  
MODE DB2ROW 
Begin
DECLARE v_CourseCount integer;
DECLARE v_MaxEnroll integer;
DECLARE v_error_msg VARCHAR(256);
  --determine number of people enrolled
  SELECT COUNT(*) INTO v_CourseCount FROM HRPEMPLCRSSCH WHERE CRSSCHID = n.CRSSCHID;
  --determine max attendess
  SELECT MAXATTENDEES INTO v_MaxEnroll FROM HRPCRSSCHED WHERE SCHEDULEID = n.CRSSCHID;

  If v_MaxEnroll = v_CourseCount then
    set v_error_msg = 'Maximum Attendance reached.';
    SIGNAL SQLSTATE VALUE 'SA001' SET MESSAGE_TEXT = v_error_msg;
  End If;
End;

I want to make sure that if a course has a MaxAttendee of 20 then once 20 people have signed up, don't allow any others to register.

So as long as Max_enroll doesn't equal CourseCount then the record will be inserted. If they are equal, the insert fails.

Thanks for any pointers/suggestions/answers!

Leslie

 

[blom0344]

I have no immediate comment on the code (looks plausible), but shouldn't you take into account people unregistering at any time? You are simply counting records without making sure the registrations are still valid.
You could end up with 17 attendees cause you're trigger sees 20 this way (while 3 have annulled the registration)

How about:

SELECT COUNT(distinct PERSON) INTO v_CourseCount FROM HRPEMPLCRSSCH WHERE CRSSCHID = n.CRSSCHID
AND STATUS = 'Registered';

Counting distinct individuals correct for possible dual entries (but perhaps this is taken care of by constraints)

Ties Blom

 

[lespaul]

Yes there's some other scenarios that need to be accounted for, but I wanted to make sure I was on the right track and that the trigger would do what I expected.

Thanks for the confirmation!

Leslie

 

[blom0344]

Please make sure that you post the ultimate trigger code when you have a solid,working implementation. These are snippets that are very worthwhile for future reference! (at least they are taken directly from day-to-day work)

Ties Blom