User defined events

User defined events are supported on several other databases in different form and semantics, few examples

• IBM solidDB http://pic.dhe.ibm.com/infocenter/solidd/v6r5/index.jsp?topic=%2Fcom.ibm.swg.im.soliddb.sql.doc%2Fdoc%2Fsolid.sql.syntax.html
• PostgreSQL http://www.postgresql.org/docs/9.3/static/sql-createeventtrigger.html
• Oracle http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/triggers.htm#LNPLS020
• Transact-SQL http://technet.microsoft.com/en-us/library/ms189453.aspx

Events are used to signal an named event in the database. Applications can use named events instead of polling, which uses more resources.

Event is created with

CREATE EVENT event_name [parameter_list] 

The name can be any user-specified alphanumeric string. The parameter list specifies parameter names and parameter types. The parameter types are normal SQL types.

Events are dropped with

DROP EVENT event_name 

Events are sent and received inside stored procedures. Special stored procedure statements are used to send and receive events.

The event is sent with the stored procedure statement

post_statement ::= POST EVENT event_name

    [( parameters) ] 

Event parameters must be local variables, constant values, or parameters in the stored procedure from which the event is sent. All clients that are waiting for the posted event will receive the event. Each connection has its own event queue. The events to be collected in the event queue are specified with the stored procedure statement:

wait_register_statement ::= REGISTER EVENT event_name 

Events are removed from the event queue with the stored procedure statement:

wait_register_statement ::= UNREGISTER EVENT event_name 

Note that you do not need to register for every event before waiting for it. 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 don't want to start WAITing for them until later.

To make a procedure wait for an event to happen, the WAIT EVENT construct is used in a stored procedure:

wait_event_statement ::=

   WAIT EVENT

      [event_specification ...]

   END WAIT

event_specification ::=

   WHEN event_name [(parameters)] BEGIN

      statements

   END EVENT

Each connection has its own event queue. To specify the events to be collected in the event queue, use the command REGISTER EVENT event_name. Events are removed from the event queue by the command UNREGISTER EVENT event_name.

"CREATE PROCEDURE register_event

begin

        register event test_event

end";

"CREATE PROCEDURE unregister_event

begin

        unregister event test_event

end";