How do I trigger an EVENT at some single point in the near future?

[Breck_Carter]

How do I create an event that will run exactly once at some point in the near future?

Something akin to "TRIGGER EVENT ... IN 10 SECONDS".

Will CREATE EVENT ... SCHEDULE START DATE 'hh:mm:nn:ss' do the trick, if hh:mm:nn:ss is a few seconds in the future? It's important for the event to run one time, not every day or some such nonsense... the docs are beyond inscrutable or I'm beyond stupid, one or t'other 🙂

---

Update: This seems to work, but is it gonna fire again?

CREATE EVENT fire_once 
HANDLER BEGIN
   MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, 
      ' Event fire_once has executed. ' ) TO CONSOLE;
END; 

BEGIN
DECLARE @sql LONG VARCHAR;
SET @sql = STRING ( 'ALTER EVENT fire_once ADD SCHEDULE START TIME ''', DATEADD ( SECOND, 10, CURRENT TIMESTAMP ), '''' );
   MESSAGE STRING ( 'DIAG ', CURRENT TIMESTAMP, 
      ' @sql = ', @sql ) TO CONSOLE;
EXECUTE IMMEDIATE @sql;
ALTER EVENT fire_once ENABLE;
END;

DIAG 2015-01-21 16:39:29.657 @sql = ALTER EVENT fire_once ADD SCHEDULE START TIME '2015-01-21 16:39:39.656'
DIAG 2015-01-21 16:39:39.045 Event fire_once has executed. 

[Former Member · Accepted solution]

It should be a one-time event unless you add an Every or On clause.

From the V16 DCX article on Create Event (Schedule Clause)

"A scheduled event is recurring if its definition includes EVERY or ON; if neither of these reserved words is used, the event executes at most once. . . . When a non-recurring scheduled event has passed, its schedule is deleted, but the event handler is not deleted. "

[VolkerBarth · Accepted solution]

So Breck's ALTER EVENT block could be used once again to make that event run once again.

And the obligatory Besserwisser addition:

• What does happen if the START TIME is already passed when the schedule is added (and it's no recurring event) - is the schedule then immediately deleted (and basically a NO-OP)? - No, it raises SQLCODE -1128 "Start date/time for non-recurring event 'fire_once' is in the past." Well done, as usual:)