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?

asked 17 Feb, 07:47

Sako's gravatar image

Sako
1.0k405595
accept rate: 25%


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.

permanent link

answered 17 Feb, 08:29

Volker%20Barth's gravatar image

Volker Barth
36.1k342501749
accept rate: 34%

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

(17 Feb, 08:38) Sako
Replies hidden

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

(17 Feb, 08:51) Sako

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.

(17 Feb, 09:08) Volker Barth

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

Thank you!!

(17 Feb, 09:13) Sako

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'

(17 Feb, 11:24) Sako

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.

(17 Feb, 11:38) Volker Barth

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?

(17 Feb, 12:06) Sako
1

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.
(17 Feb, 12:29) Volker Barth
1

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

(17 Feb, 13:25) Sako

Wow, that's really nice, thank you!

(17 Feb, 13:39) Volker Barth
1

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

(18 Feb, 08:00) Breck Carter
Replies hidden
1

Thank you @Breck!!

Here I can learn HTML too!!

(18 Feb, 09:51) Sako
showing 2 of 12 show all flat view
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:

×70
×5
×3

question asked: 17 Feb, 07:47

question was seen: 159 times

last updated: 18 Feb, 09:52