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...
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.
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. "
answered 21 Jan '15, 18:02
Nick Elson S...