Question Re: Automatic Commit statements within Triggers in Technology Q&A

Automatic Commit statements within Triggers

Baron — Mon, 17 Feb 2020 07:47:31 GMT

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?

Re: Automatic Commit statements within Triggers

VolkerBarth — Mon, 17 Feb 2020 08:29:02 GMT

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.

Re: Automatic Commit statements within Triggers

Baron — Mon, 17 Feb 2020 08:38:15 GMT

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

Re: Automatic Commit statements within Triggers

Baron — Mon, 17 Feb 2020 08:51:07 GMT

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

Re: Automatic Commit statements within Triggers

VolkerBarth — Mon, 17 Feb 2020 09:08:16 GMT

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.

Re: Automatic Commit statements within Triggers

Baron — Mon, 17 Feb 2020 09:13:46 GMT

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

Thank you!!

Re: Automatic Commit statements within Triggers

Baron — Mon, 17 Feb 2020 11:24:08 GMT

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'

Re: Automatic Commit statements within Triggers

VolkerBarth — Mon, 17 Feb 2020 11:38:52 GMT

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.

Re: Automatic Commit statements within Triggers

Baron — Mon, 17 Feb 2020 12:06:17 GMT

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?

Re: Automatic Commit statements within Triggers

VolkerBarth — Mon, 17 Feb 2020 12:29:34 GMT

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.

Re: Automatic Commit statements within Triggers

Baron — Mon, 17 Feb 2020 13:25:01 GMT

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!!!!

Re: Automatic Commit statements within Triggers

VolkerBarth — Mon, 17 Feb 2020 13:39:44 GMT

Wow, that's really nice, thank you!

Re: Automatic Commit statements within Triggers

Breck_Carter — Tue, 18 Feb 2020 08:00:44 GMT

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

Re: Automatic Commit statements within Triggers

Baron — Tue, 18 Feb 2020 09:51:29 GMT

Thank you @Breck!!

Here I can learn HTML too!!