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

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?

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.

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

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.)

...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?

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.

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.

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. ;-)

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.

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.

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