Solution to a problem

I received an email this morning with a link to a Linked-In post that pointed to someone's BLOG.  The Blogger was proposing a solution to a problem someone had presented to him at work that he thought was rather elegant and so posted it for the consumption and edification of the Linked-In community.  The problem:  In a single SQL statement register an attendee for an event unless the attendee is already registered for an overlapping event.  The solution was, to me, overly complex and absolutely non-portable in that it takes advantage of Oracle's conditional INSERT statement, so I said to myself: "How would I solve this better and in a fairly portable fashion?"  So, presented here is my solution:

Setup:
-- Table attendee( attendee_id serial, name char(40) );
-- Table event( event_id serial, title char(80), start_time datetime year to minute, end_time datetime year to minute, location char(10) );
-- Table attendance( attendee_id int, event_id int, register_time datetime year to fraction(3), attended boolean );

Assume all tables have the appropriate RI constraints and indexes in place.

I propose that a simple INSERT TRIGGER (and yes an UPDATE trigger would also be appropriate but I leave that to the reader as an exercise) is the correct, simple, and portable solution:

CREATE PROCEDURE attendee_check() REFERENCING NEW AS neu FOR attendance;

DEFINE clash INT;

       SELECT count(*)
       INTO clash
       FROM attendee AS a, event AS e1, attendance AS ae, event AS e2
       WHERE a.attendee_id = neu.attendee_id
         AND e1.event_id = neu.event_id
         AND ae.attendee_id = a.attendee_id
     AND e2.event_id = ae.event_id
     AND e1.start_time <= e2.end_time
     AND e1.end_time >= e2.start_time
     AND e2.event_id != e1.event_id;

       IF (clash > 0) THEN
             RAISE EXCEPTION -746, 0, "Overlapping Event Rejected!";
       END IF

       RETURN;

END PROCEDURE;


CREATE TRIGGER attend_trig_i INSERT ON attendance
       REFERENCING NEW as neu
       FOR EACH ROW
       ( EXECUTE PROCEDURE attendee_check( ) WITH TRIGGER REFERENCES );



-- Attendees
insert into attendee( 0, 'Art' );
insert into attendee( 0, 'Fred' );
insert into attendee( 0, 'Barney' );

-- Events
insert into event values( 0, 'Event 1',  '2011-12-28 09:00', '2011-12-28 10:00', 'Here');
insert into event values( 0, 'Event 2',  '2011-12-28 09:00', '2011-12-28 10:00', 'There');
insert into event values( 0, 'Event 3',  '2011-12-28 09:30', '2011-12-28 10:30', 'Elsewhere');
insert into event values( 0, 'Event 4',  '2011-12-28 10:30', '2011-12-28 11:30','Elsewhen');

-- Test:
> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 1, current, 'f' );

1 row(s) inserted.

> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 4, current, 'f' );
1 row(s) inserted.

> insert into attendance (attendee_id, event_id, register_time, attended ) values ( 1, 1, current, 'f' );
  268: Unique constraint (informix.attendance_pk) violated.

  100: ISAM error:  duplicate value for a record with unique key.
Error in line 1
Near character position 51
> insert into attendance (attendee_id,event_id,  register_time, attended ) values ( 1, 2, current, 'f' );

  746: Overlapping Event Rejected!
Error in line 1
Near character position 1
> insert into attendance ( attendee_id, event_id, register_time, attended ) values ( 1, 3, current, 'f' );

  746: Overlapping Event Rejected!
Error in line 1
Near character position 1
>
select * from attendance;

attendee_id    event_id signup_time             attended

          1           1 2011-12-14 10:48:11.221        f
          1           4 2011-12-14 10:49:21.361        f

2 row(s) retrieved.

>

Simple, elegant, and except for the details of the syntax of the stored procedure (and in some cases the trigger) it is portable and can be implemented in most RDBMSes, unlike the original solution.  The final advantage is that this solution will prevent overlapping registrations no matter who writes the application making the insert which is now a simple insert.  The application developer is not required to know in advance that he has to meet this requirement (as obvious as it should be ;-).