--- In firebird-support@yahoogroups.com, Johannes Pretorius
<johannes@...> wrote:

>
> Good day all
> -=00=-=-00-=-=0
>
> Thanks to all the help that was given so far. Also thanks for your

patience with me.

>
> This is also related to "Transaction and Stored procedures."
>
> I have 2 topics with questions
> ---------------------------
> A) Does triggers on the database only get triggers when the

transaction is commited ? Or can I assume that it

> gets run inside of the transaction also. If this is the case and

if I call stored procedures inside those triggers

> to perform certain function that will define the next step in

logic in my program code. Does these stored procedures

> that is called via the databases before of after insert or

update also exist inside my transaction ?

The triggers are fired at the moment each action on the record occurs,
not when committing. Just like stored procedures, anything done by a
trigger occurs within the context of the transaction that ran the
query that is causing the trigger to fire.

There are 6 different types of triggers

BEFORE INSERT
AFTER INSERT
BEFORE UPDATE
AFTER UPDATE
BEFORE DELETE
AFTER DELETE

A change to any field in the record will fire the trigger (even a
dummy update, eg update tableA set ID=ID where ID=1)

Use whichever ones you need. You can access the value of the fields in
the triggers.

OLD.<FIELDNAME> and/or NEW.<FIELDNAME>

Obviously OLD.<FIELDNAME> is not available in insert triggers and
NEW.<FIELDNAME> is not available in delete triggers

You can only change the value of a field in a BEFORE trigger (obviously).

>
> B) Locking of records. As previously explained with transaction and

data stored on pages and then commited to database if all is fine etc.

I avoid calling it 'locking' because that can be confusing. MGA by
definition is not locking. Most of the logic can be traced back to two
transactions can not modify the same record at the same time.

> i) When a record is locked thanks to my transaction. Is it a

row level (record) or page level (all active ones on the cache page)
lock ?

When your transaction changes a record, then no other transactions can
change that record until you commit or rollback. They are free to
modify other records on the same page.

> ii) What will lock the record ? only updates , inserts and

deletes or does normal selects also lock records ?

For inserts, it is not applicable as no-one can see the newly inserted
value until you commit, and once you commit, the record is free to be
modified by another transaction.

Updates and Deletes are affected.

Selects are not affected unless you specifically use the WITH LOCK as
defined in the release notes. One of the best things about MGA is that
readers dont block readers or writers and writers don't block readers.

Firebird provides two mechanisms to handle lock conflicts. The
transaction can be told to WAIT (hope that the other rolls back) or to
NO_WAIT (give up immediately). Which one is appropriate depends on
your business requirements.

> iii) If I only start a transaction and then commit it

immediately afterwards with absolutely nothing between it. Does a
database lock appear at all ?

Not in the sense you are thinking. I think it does lock to write to
the TIP (transaction inventory page), but that is just to tell the
database that other transactions can treat your changes as committed.

Adam