SQLite Forum

Can I assume that the TRIGGER runs from within my transaction?
Login

Can I assume that the TRIGGER runs from within my transaction?

(1) By PazO (JohnSmith) on 2021-12-02 17:30:06 [link] [source]

Hello dear forum,

Assuming my TRIGGER was fired due to code running from within a transaction: can I assume TRIGGER code will also run from within this transactions?

Farther more: if I ROLLBACK this transaction, will all TRIGGER changes be undone together with the transaction changes?

Thanks!

PazO

(2) By Larry Brasfield (larrybr) on 2021-12-02 17:56:34 in reply to 1 [link] [source]

Yes and Yes.

(3) By PazO (JohnSmith) on 2021-12-16 08:15:31 in reply to 2 [link] [source]

One more question about this:

Suppose I execute SQL command not from within a transaction, and suppose this command will fire a TRIGGER, will these two commands - my command and the TRIGGER - will run from within a (dynamic) transaction, or will they be executed one after the other, allowing other commands/transactions to be executed between them?

(4) By Larry Brasfield (larrybr) on 2021-12-16 08:31:28 in reply to 3 [link] [source]

There is no such thing in SQLite as "execute SQL command not from within a transaction". If an explicit transaction has not been started, then each statement's execution (the stepping part) is done within an automatic transaction. See section 2 on transactions.

(5) By Scott Robison (casaderobison) on 2021-12-16 08:36:10 in reply to 4 [link] [source]

All true, but I think the gist of the question was: "In the case of automatic transactions, will my SQL statement run in the same transaction as the trigger, or will the SQL statement be run in one transaction which is committed before the trigger runs in a separate transaction?"

(6.1) By Larry Brasfield (larrybr) on 2021-12-16 08:43:05 edited from 6.0 in reply to 5 [link] [source]

I almost added such words, but they flow so inexorably from the fact of trigger actions running within the same transaction as their triggers that it seemed too obvious to say. IOW, posts #2 and #4 suffice to answer question #3.

At any rate, (to cut short discussion of an explicit answer): Yes (to Scott's framing of the question.)

(9) By Scott Robison (casaderobison) on 2021-12-16 08:52:43 in reply to 6.1 [link] [source]

So to summarize: If one wants to ensure that a SQL query and a related trigger are run within the same transaction, put them in an explicit transaction. Anything else is presumably an implementation detail which could in theory change in the future and is not guaranteed, much as row order from a query is not guaranteed unless one explicitly requests a given order.

(10) By Larry Brasfield (larrybr) on 2021-12-16 08:58:18 in reply to 9 [link] [source]

The docs on triggers make quite clear that their effects occur within the same transaction. You might note (if you look it up, which I'm too busy to do just now,) that among possible trigger action effects are rollback of the transaction. That would be challenging if the transaction was already committed.

(I suspect you're yanking my chain a little here. I hope it has been fun. ;-)

(11.1) By Scott Robison (casaderobison) on 2021-12-16 17:51:16 edited from 11.0 in reply to 10 [source]

I'm not above chain yanking on occasion, I was just trying to interpret the words I was reading. I kind of thought the same when you said "yes" to my reformulation of the updated question, since I posted an either / or scenario. My interpretation of that was "yes, one of those two things happens, and I'm not committing to one or the other, which means it is undefined as to which path is taken". It is not unlike how I'll answer questions from my children at times. "Can we do X or do we have to do NOT X?" they might ask, and I'll reply "Yes."

Note that I've rarely used triggers, and didn't look up documentation, was just trying to be helpful by clarifying what I thought the question really was and what your answer really meant.

(12) By Larry Brasfield (larrybr) on 2021-12-16 17:14:57 in reply to 11.0 [link] [source]

Hmmm. You have helped me demonstrate why wee-hour posting is ill-advised unless sleep cycle is shifted. My "Yes" was to your first alternative (in my sloppy re-reading of that framing.)

As for "yes" to conflicting alternatives: Snark is not below me, but I tend to do it more obviously.

On the alternatives themselves: Such critically important behavior (as whether trigger actions are in the same transaction as their triggering conditions) should never be left unstated in the docs. The possibility of that being intentionally left undocumented was not conceivable to me that early in the day.

So, sorry for the chain-yanking suggestion.

(13) By Scott Robison (casaderobison) on 2021-12-16 17:50:32 in reply to 12 [link] [source]

No worries! If this is the worst thing that happens to me today, it'll have been a really good day. :)

(7) By PazO (JohnSmith) on 2021-12-16 08:42:53 in reply to 4 [link] [source]

...but I can also interpret the above as:

  1. My not-explicit-transaction command will be automatically encapsulated in a transaction,

  2. Sometime later - the TRIGGER code will be encapsulated in other transaction

My question is whether I can assume that my command and the TRIGGER cod will be encapsulated by the same transaction?

(8) By Larry Brasfield (larrybr) on 2021-12-16 08:51:26 in reply to 7 [link] [source]

They have to be so encapsulated, for several reasons: (1) triggers would be close to useless otherwise; (2) said encapsulation is necessary because there is nothing to perform subsequent execution after an transaction ends. The library operates by means of calls made into it. Finally (3), it would be pure folly for trigger handling within an explicit transaction to group differently with the instigating conditions for an explicit transaction than for an automatic transaction. It would take extra code to obtain a worse, useless and derision-worthy result.