Automatic Commit statements within Triggers

[Baron]

Hi, I need to execute an automatic commited statement (alter event statement) within a trigger of a table.

I get an error message stating: COMMIT/ROLLBACK not allowed within atomic operation! Error Code = -267, SQL State = 42000.

Is there any workaround to get this working?

[VolkerBarth · Accepted solution]

A trigger runs as atomic part of the triggering DML statement (i.e. the according INSERT/UPDATE/DELETE/MERGE statement) and as such cannot do a commit on its own, it must be part of the trancsaction the according statement belongs to. (Aside: Within a trigger you can use the ROLLBACK TRIGGER statement, and that will also rollback the according statement.)

However, you could start an event from within your trigger, and events run in their own connections and therefore in their own transactions. There are several discussions in this forum dealting with such setups.

[Baron · Accepted solution]

OK, but back to my question, is there a workaround to execute an ALTER EVENT statement from within a trigger?

[Baron · Accepted solution]

Should I then understand from your answer, that within the trigger I should start another event TRIGGER EVENT MYHELPEVENT, and then alter my target event from within this MYHELPEVENT? ALTER MYTARGETEVENT

[VolkerBarth · Accepted solution]

Yes, as an ALTER EVENT like most (or all?) DDL statement issues an automatic commit, you cannot run that from your trigger code - but you could trigger another event that itself calls ALTER EVENT.

You could even use the EVENT_PARAMETER function to supply the according information from the trigger to the event.

See a sample there for the latter.

[Baron · Accepted solution]

OK, thank you for the information. It will be a complicated solution, but at least it works!!

Thank you!!

[Baron · Accepted solution]

EVENT_PARAMETER would not be helpful, since my point is not to change the event itself, but the scheduler.

Something like:

ALTER EVENT MYTARGETEVENT SCHEDULE START TIME '20:00'

[VolkerBarth · Accepted solution]

Well, you could use the parameter to specify the start time (or whatever has to be changed), unless that information is available other wise. You could also use several parameters to specify both the event name and the start time or whatever, so your "helper event" could alter different events...

It's just a way to "transport information" between the invoker of the event (here your trigger) and the event itself. Of course you could also use database-scope variables (in v17) or (possibly temporary share by all) tables to do so.

[Baron · Accepted solution]

I still have problem with the first approach (trigger event from within a trigger.

I get the following error; Permission denied: you do not have permission to use the "TRIGGER EVENT" statement Error Code=-121, SQL state=42000

Despite I am connected as DBA!

Any ideas please?

[VolkerBarth · Accepted solution]

Triggers execute using the privileges of the owner of the table on which they are defined, not the privileges of the user who caused the trigger to fire, and not the privileges of the user who created the trigger.

If the table owner is not the one who created the event, I guess you need to

• either give the table owner the MANAGE ANY EVENT system privilege or
• wrap the TRIGGER EVENT statement within a procedure (owned by the event creator) and allow the table owner the execution of that wrapper procedure, and call that procedure from your trigger.

[Baron · Accepted solution]

Only if I knew how to insert photos in this page!! https://www.quora.com/What-does-the-term-hats-off-mean

Respect and Thanks!!!!

[VolkerBarth · Accepted solution]

Wow, that's really nice, thank you!

[Breck_Carter · Accepted solution]

Here is how to insert an image into a comment: Use naked HTML like this...

<img src="https://qph.fs.quoracdn.net/main-qimg-94e93244063f7294636ee10532cc5896.webp" width="200">

You're limited to using links to images; you cannot upload images to this forum any more.

Also, the width="xxx" attribute is very handy to make giant images fit into comments.

Another tip: If you are trying to show HTML in a PRE tab (like above), change all embedded "<" characters to "&lt;"

[Baron · Accepted solution]

Thank you @Breck!!

Here I can learn HTML too!!