Subject Re: DDL and DML in the one transaction
Author Peter Lee
Hi Adam,

Just to clarify - mixing is a problem when you're doing DDL and DML on
the same table /proc etc - if they are separate items, then it's ok?

Thinking about this because we could then split our update process
into multiple parts, in consecutive transactions... however, we'd want
to update our db version record at the end of each, even if one
transaction had DDL.


Peter Lee

--- In, "Adam" <s3057043@y...> wrote:
> > Adam mentioned in the post 'dbExpress and transactions' that mixing
> DDL and
> > DML in the one transaction will cause 'funny' things to happen, can
> someone
> > please elaborate on this - what's going to / could happen?
> >
> A lot of the time you will get away with it, the problems start when
> you create a field in a table then try and populate it with data
> before you commit, that sort of thing.
> If you add a field to TableA, then update TableB (and TableB has no
> triggers that affect TableA), then there is no problem.
> Values that you **think** are populated may not be. Give it a go
> yourself one day when you are bored. Queries like update table set
> somedate = 'now' also behaves unexpectedly.
> In short, it is more likely to cause data inconsistencies than some
> half upgraded database.
> Adam