Is it safe to assume EVENT_PARAMETER ( 'context-name' ) returns NULL if context-name doesn't apply / doesn't exist? The docs in V11 and V12 are silent on the subject.

In particular, is the following a safe technique for determining if a scheduled event has been fired because of the schedule or because of an explicit TRIGGER EVENT statement?

...and, is there an easier way to answer that question?

I want to be able to turn the schedule on and off at runtime but still allow TRIGGER EVENT (ALTER EVENT doesn't help, and I don't want to use it anyway).

CREATE EVENT ev_every_10_seconds
SCHEDULE START TIME '00:00' EVERY 10 SECONDS
HANDLER BEGIN

MESSAGE STRING ( 
      CURRENT TIMESTAMP, 
      ' ev_every_10_seconds @explicitly_triggered = "',
      COALESCE ( EVENT_PARAMETER ( '@explicitly_triggered' ), 'No' ),
      '"' ) TO CONSOLE;

END;

TRIGGER EVENT ev_every_10_seconds ( @explicitly_triggered = 'Yes' );

2010-10-31 14:39:30.031 ev_every_10_seconds @explicitly_triggered = "No"
2010-10-31 14:39:40.093 ev_every_10_seconds @explicitly_triggered = "No"
2010-10-31 14:39:49.437 ev_every_10_seconds @explicitly_triggered = "Yes"

asked 31 Oct '10, 18:50

Breck%20Carter's gravatar image

Breck Carter
25.6k427586844
accept rate: 20%


The event_parameter function will return NULL if the parameter is not a recognized value.

Another way to distinguish events fired via a schedule versus ones fired via TRIGGER EVENT is to test the value of event_parameter('ScheduleName'). If the event was fired via a schedule, the value will be a non-empty string representing the name of the schedule. If fired via TRIGGER EVENT, the value will be an empty string.

permanent link

answered 01 Nov '10, 13:58

Bruce%20Hay's gravatar image

Bruce Hay
2.6k1510
accept rate: 48%

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:

×80

question asked: 31 Oct '10, 18:50

question was seen: 556 times

last updated: 01 Nov '10, 13:58