oracle - Abort insert/update operation in trigger using PL/SQL -

i write trigger checks information inserted/updated, compare them data database , if not correct, stops whole operation. wrote before trigger (for each) , threw application exception if wrong, not working, becouse read table updated, ora-04091 error.

and wondering how solve this? idea of mine write before trigger insert necessary data package , read them after trigger won't each. there's problem how abort edition? if make rollback undo operations in transaction think not smart. how solve problem?

don't go there.

ora-04091: table xxxx mutating indicator whatever you're trying complex done reliably triggers.

sure, use package array variable , handful of triggers (ugh!) around error, code likely:

• be unmaintainable due complexity , unpredictable nature of triggers
• not respond multi-user environment

this why should re-think approach when encounter error. advise build set of procedures nicely grouped in package deal inter-row consistency. revoke privileges dml table directly , use procedures modify it.

for instance update procedure atomic process would:

1. acquire lock prevent concurrent update on same group of rows (for instance lock room record in hotel reservation application).
2. check row inserted validates business logic
3. make relevant dml
4. rollbacks changes (and changes -- not whole transaction) in case of error (easy pl/sql, raise error).