CODEWITHSUNDEEP
oracle-database
TownCube
TownCube

Reputation: 1310

Keeping tables consistent during trigger execution?

I have a trigger that checks another couple of tables before allowing a row to be inserted. However between the time I check the other tables and insert the row the other tables may get updated.

How do I ensure the tables I'm checking remain in a consistent state until after the new row is inserted? I was thinking of taking locks out but everything I've read boils down to if you are not leaving locking to Oracle you're almost certainly doing it wrong.

Upvotes: 0

Views: 71

Answers (1)

Matthew Watson
Matthew Watson

Reputation: 14233

Oracle is already doing this for you, when you perform a select it will look at all tables as of the time the transaction started ( the time of the first DML ). This wont stop the data from being changed under you though, your transaction just wont see it being changed. If you want to stop that data from being changed then you can use "SELECT FOR UPDATE" as Justin Cave suggests.

I would seriously question what you are doing though, triggers, except in the most trivial cases, almost always lead to unexpected side effects.

Upvotes: 2

Related Questions