Hi,

> > 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 record
> and then throw exception. The exception had to be handled though. or
> the exception force the rollback of the transaction? maybe then
> commit before throwing an exception?

If the exception "falls through" (isn't handled in the trigger), all
work of the current action is being cancelled (note: this is not
the same as a "rollback"). You can do whatever you like with
the transaction that started the action.

> 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
> trigger'.
>
> I agree that a stored procedure is more elegant, but still it is
> possible to do it via trigger, am I right?

No, you're wrong.

> 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
> transaction?

You can't - these will be two seperate transactions. One
cannot see the data from the other...

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com