With SA 126.96.36.1998, in sa_make_object, does anyone know if there is a particular reason why for event objects, the procedure drop the event if it exists, instead of only creating it like other object type ? This cause a problem because if an event has a schedule (or is a system event), calling sa_make_object for the event cause the schedule(s) to be dropped.
There is really no good answer to say why sa_make_object() does what it does for event objects but there is a reason:
I'll explain by giving some history - sa_make_object was created back in the days when the OR REPLACE clause did not exist and (obviously therefore) was not supported. The procedure was added as an easy way to allow SQL 'build' scripts to be run over and over again (by developers) during implementation phase of a project. When you use sa_make_object in this scenario - i.e. development (not production) - then it does not matter much because the very next statement in the SQL script is always an ALTER EVENT that defines the real values for the event.
If you are wanting to alter an event in a production system - as you have described - then you should just use ALTER EVENT to do this with no preceding call to sa_make_object().
To respond to Volker's comment about the 'bug' in the sa_make_object implementation - no it is not a bug since events do not have owners. (Yes the CREATE and ALTER EVENT statements accept an owner in the definition but the owner value is ignored - if anything the 'bug' is that these statements should not accept an owner field... but we're stuck with this since we would break backward compatibility). Note that events are executed by the creator, the user that is creating the event, and this is explained in the CREATE EVENT notes. Note the docs do not make it clear that the owner is ignored (I will let the doc team to add this note).
Why is there no OR REPLACE clause on the CREATE EVENT statement? An oversight perhaps? (and it is on the list for a future version). But note that the OR REPLACE clause of CREATE EVENT is likely going to just do a DROP EVENT and then a CREATE EVENT under the covers so does not really resolve the issue.
FWIW: For reasons explained above, I would recommend anyone that is using events to always just make the HANDLER portion of the event just be a call to a procedure. Doing this resolves a number of things: (a) you end up not needing to change the event definition very often since most changes are to the logic (i.e. procedure) and hence don't run into the issues asked in this question, and (b) you have better control which owner the procedure is executed as (i.e. owner of the procedure), and (c) ... well I can't tell you about c (yet ;-)