SQL Guide : SQL extensions : Events : Using events: Example 3
  
Using events: Example 3
This example shows a simple usage of the REGISTER EVENT and UNREGISTER EVENT commands.
Example 1 and Example 2 do not use REGISTER EVENT, yet their WAIT commands succeeded anyway. The reason for this is that when you wait on an event, you will be registered implicitly for that event if you did not already explicitly register for it. Thus you only need to explicitly register events if you want them to start being queued now but you do not want to start WAITing for them until later.
CREATE EVENT e0;
CREATE EVENT e1 (param1 int);
COMMIT WORK;
-- Create a procedure to register the events to that when they occur
-- they are put in this connection's event queue.
"CREATE PROCEDURE eeregister
BEGIN
  REGISTER event e0;
  REGISTER EVENT e1;
END";
CALL eeregister;
COMMIT WORK;
-- Create a procedure to post the events.
"CREATE PROCEDURE eepost
BEGIN
  DECLARE x int;
  x := 1;
  POST EVENT e0;
  POST EVENT e1(x);
END";
COMMIT WORK;
-- Post the events. Even though we haven't yet waited on the events,
-- they will be stored in our queue because we registered for them.
CALL eepost;
COMMIT WORK;
-- Now create a procedure to wait for the events.
"CREATE PROCEDURE eewait RETURNS (whichEvent VARCHAR(100))
BEGIN
  DECLARE i INT;
  WAIT EVENT
    WHEN e0
      BEGIN
        whichEvent := 'event0';
    END EVENT
    WHEN e1(i)
      BEGIN
        whichEvent := 'event1';
  END EVENT
END WAIT
END";
COMMIT WORK;
-- Since we already registered for the 2 events and we already
-- posted the 2 events, when we call the eewait procedure twice
-- it should return immediately, rather than waiting.
CALL eewait;
CALL eewait;
COMMIT WORK;
-- Unregister for the events.
"CREATE PROCEDURE eeunregister
BEGIN
  UNREGISTER event e0;
  UNREGISTER EVENT e1;
END";
CALL eeunregister;
COMMIT WORK;
CREATE EVENT e0;
CREATE EVENT e1 (param1 int);
COMMIT WORK;
-- Create a procedure to register the events to that when they occur
-- they are put in this connection's event queue.
"CREATE PROCEDURE eeregister
BEGIN
  REGISTER event e0;
  REGISTER EVENT e1;
END";
CALL eeregister;
COMMIT WORK;
-- Create a procedure to post the events.
"CREATE PROCEDURE eepost
  BEGIN
    DECLARE x int;
    x := 1;
    POST EVENT e0;
    POST EVENT e1(x);
  END";
COMMIT WORK;
-- Post the events. Even though we haven't yet waited on the events,
-- they will be stored in our queue because we registered for them.
CALL eepost;
COMMIT WORK;
-- Now create a procedure to wait for the events.
"CREATE PROCEDURE eewait RETURNS (whichEvent VARCHAR(100))
  BEGIN
    DECLARE i INT;
    WAIT EVENT
      WHEN e0 BEGIN
      whichEvent := 'event0';
    END EVENT
    WHEN e1(i) BEGIN
      whichEvent := 'event1';
    END EVENT
  END WAIT
END";
COMMIT WORK;
-- Since we already registered for the 2 events and we already
-- posted the 2 events, when we call the eewait procedure twice
-- it should return immediately, rather than waiting.
CALL eewait;
CALL eewait;
COMMIT WORK;
-- Unregister for the events.
"CREATE PROCEDURE eeunregister
BEGIN
  UNREGISTER event e0;
  UNREGISTER EVENT e1;
END";
CALL eeunregister;
COMMIT WORK;
See also
Events