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. 

asked 21 Jan '15, 16:14

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 22 Jan '15, 08:13


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. "

permanent link

answered 21 Jan '15, 18:02

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 31%

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:)
(22 Jan '15, 03:44) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×35

question asked: 21 Jan '15, 16:14

question was seen: 406 times

last updated: 22 Jan '15, 08:13