Subject Re: [firebird-support] Re: Insert Trigger which makes an Update if pk allready there possible?
Author Helen Borrie
At 02:48 PM 20/08/2004 +0000, you wrote:
> >
> > No, a trigger acts as part of a specific operation (I/U/D) so a
> > can't change the operation to be "insert" if it was "update" or
>to "update"
> > if it was "insert". All you could do here would be to have a
>routine to
> > create a different value for the PK and thus prevent the key
> > violation; but this isn't what you want here.
>is it possible to stop execution of the operation in the 'before
>trigger' that is part of this operation?
>this is done by exception throwing, correct?

Yes; throwing an exception is the only way to stop execution.

>I am doing updates in triggers, so it is possible to update a record
>and then throw exception. The exception had to be handled though. or
>the exception force the rollback of the transaction?

No, transactions are atomic. An exception will stop execution of the
trigger, cause it to look for a handler and, if no handler is found, return
control to the calling client. You can't roll back or commit transactions
in triggers and SPs. If a trigger exception isn't handled, the i/u/d
request simply fails. The transaction is left neither committed nor rolled
back. It is up to the client application to decide what to do.

>maybe then commit before throwing an exception?

Not possible.

>Or to avoid an exceptions throwing setup a dummy key (say of a value
>less then 1000 and greater than existing dummy records values) and
>delete all records from keys range 1-1000 as a part of 'after

I don't get this. In a trigger, if you can't solve the problem by handling
it, you *want* the exception to end the operation. That's the point of

>I agree that a stored procedure is more elegant, but still it is
>possible to do it via trigger, am I right?

No, it's not possible to do it via a trigger. If the row already exists,
you can't insert it again. So it's correct for a trigger to cause the
operation to fail. And it's correct to use a SP if you want to perform a
conditional insert.

>a question by the way are transactions flat or hierarchical - ie. if
>I aopen a transaction from within another transaction and commit the
>inside one, will it be rolled back if I recall the outside

I guess by your definition, transactions are "flat". You can have multiple
transactions running in parallel, but you can't start a transaction from
within a transaction. On the client side (in v.1.5+) you can have
savepoints in transactions and rollback to savepoints, i.e. roll back some
work and retain others. On the server side, the exception mechanism is
nested, so each "begin" statement within a SP or trigger is potentially a