| Subject | Re: [firebird-support] Re: Insert Trigger which makes an Update if pk allready there possible? |
|---|---|
| Author | Helen Borrie |
| Post date | 2004-08-20T15:10:39Z |
At 02:48 PM 20/08/2004 +0000, you wrote:
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.
it, you *want* the exception to end the operation. That's the point of
triggers.
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.
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
savepoint.
./heLen
> >Yes; throwing an exception is the only way to stop execution.
> > No, a trigger acts as part of a specific operation (I/U/D) so a
>trigger
> > 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?
>I am doing updates in triggers, so it is possible to update a recordNo, transactions are atomic. An exception will stop execution of the
>and then throw exception. The exception had to be handled though. or
>the exception force the rollback of the transaction?
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 valueI don't get this. In a trigger, if you can't solve the problem by handling
>less then 1000 and greater than existing dummy records values) and
>delete all records from keys range 1-1000 as a part of 'after
>trigger'.
it, you *want* the exception to end the operation. That's the point of
triggers.
>I agree that a stored procedure is more elegant, but still it isNo, it's not possible to do it via a trigger. If the row already exists,
>possible to do it via trigger, am I right?
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. ifI guess by your definition, transactions are "flat". You can have multiple
>I aopen a transaction from within another transaction and commit the
>inside one, will it be rolled back if I recall the outside
>transaction?
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
savepoint.
./heLen