At 10:12 AM 12/07/2003 +0100, you wrote:

>Thanx for ur prompt response.
>In my existing Oracle database, I've to write a statement level trigger
>becoz of th eproblem of mutating table restriction describe as under :
>
>".... the mutable table restriction on row level triggers (which says
>records in the table originating the trigger or or as a part of cascade
>delete can not be mutated by the tigger."
>
>I just wanted to know is this possible in Firebird that a trigger which is
>evoked after an insert in a row does some calculation on some other tables
>and then deletes this row itself?

It isn't clear to me what you mean by ..."and then deletes this row
itself". The quoted requirement above "mutated by the trigger" seems to
suggest not a DML operation like deleting a row, but a DDL operation like
deleting the table. As I mentioned before, you can't perform DDL from a
trigger.

If your question means "can a trigger cause the row it is operating on to
self-destruct?" then possible scenarios might include

create trigger Kamikaze for aTable
active after update position 99
as
begin
delete from aTable
where PK = new.PK;
end

I don't think this is allowed, because other DML pending on the row, but I
haven't tested it. Sounds like a fun thing to do on a wet Sunday afternoon.

Another scenario:

create trigger KilledByFamily for aTable
active after update position 100
as
begin
delete from bTable
where FK = new.PK;
end

create trigger KillMommy for bTable
active after delete position 999
as
begin
delete from aTable
where PK = old.FK;
end

If there was no referential constraint between bTable.FK and aTable.PK,
then it would not have to surmount that barrier. But I think it would
still be prevented because of the pending DML.

Ewww, perish the thought of being able to create collapsing structures like
this. It would be fun to play with it and see what damage could be done.

Wait for Ann to get up. If she's not busy with her boat, she might have
fun answering this.
:-))
heLen