--- In firebird-support@yahoogroups.com, "Rick Debay" <rdebay@r...>
wrote:

> RECREATE TABLE TABLE1
> (
> ID BIGINT NOT NULL,
> < various data columns >
> SUBMITTED DATE,
> CONSTRAINT PK_RPL_PO PRIMARY KEY (ID)
> );
>
> RECREATE TABLE TABLE2
> (
> PARENT BIGINT NOT NULL;
> )
>
> ALTER TABLE TABLE2 ADD CONSTRAINT FK_TABLE2_TABLE1
> FOREIGN KEY (PARENT) REFERENCES TABLE1 (ID)
> ON DELETE CASCADE
> ON UPDATE CASCADE
>
> CREATE TRIGGER TABLE1_NODEL FOR TABLE1 ACTIVE BEFORE DELETE

POSITION 0

> AS
> BEGIN
> IF ( OLD.SUBMITTED IS NOT NULL ) THEN
> BEGIN
> EXCEPTION E_NOT_DELETEABLE;
> END
> END
>
> Transaction one:
> Set SUBMITTED to CURRENT_DATE.
> Reads from Table2, sending data to an external source.
> If submission fails, rollback; otherwise commit.
>
> Hypothetical Transaction two:
> Delete item from Table1
>

I assume that these transactions are happenning simultaneously, or
there would be no problem.

It probably depends on your transaction type, whether it is wait or
nowait.

For records that transaction one has already updated (but not yet
committed), if it is a nowait transaction, you will immediately get
an exception on transaction 2. If it is a wait transaction, you will
get an exception when transaction 1 commits, or the delete will
succeed if transaction 1 rolls back. This is pretty easy to test,
open two isql windows to the same database and run an update on a
record then try to delete it. (I think iSQL is a wait transaction by
default)

If the delete "got there first", then the delete would succeed, and
the same sort of thing happens to the update statement.

Just to be pedantic, the trigger will always fire, just the if
statement will be false :).

I am sure there are 1000 other ifs depending on whether you are read
committed etc, but to simplify the concept, Firebird wont let two
transactions simultaneously change the same record, and depending on
the transaction settings you use, even when the first transaction
completes, the second transaction may still not be allowed to change
that record.

Adam