|
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? |
|
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. OK, but back to my question, is there a workaround to execute an ALTER EVENT statement from within a trigger?
(17 Feb '20, 08:38)
Baron
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 '20, 08:51)
Baron
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 '20, 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 '20, 09:13)
Baron
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 '20, 11:24)
Baron
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 '20, 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 '20, 12:06)
Baron
1
If the table owner is not the one who created the event, I guess you need to
(17 Feb '20, 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 '20, 13:25)
Baron
Wow, that's really nice, thank you!
(17 Feb '20, 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 "<"
(18 Feb '20, 08:00)
Breck Carter
Replies hidden
|
