Does Firebird allow SELECT in Trigger?

Dev Shed Forums Sponsor:

July 4th, 2011, 05:44 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

Does Firebird allow SELECT in Trigger?

Does anyone know if Firebird allows a SELECT query on a table during
the execution of an insert trigger on that same table?

I am looking for the least intrusive way to retrofit a rule to prevent insertions succeeding
based on certain existing conditions.

Oracle would give a mutating table error.

I want to avoid changing the code in all the client applications.

July 5th, 2011, 12:42 AM

costin_mentor

Contributing User

Join Date: Jan 2007

Posts: 31

Time spent in forums: 13 h 12 m 34 sec
Reputation Power: 7

YES, Firebird allows a SELECT query on a table during
the execution of an insert trigger on that same table.
I have a trigger like that in one of my tables.

July 5th, 2011, 12:44 AM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

Thanks.
I will give it a try.

July 8th, 2011, 08:13 AM

nagysz

Contributing User

Join Date: Jul 2006

Posts: 200

Time spent in forums: 1 Day 11 h 19 m 28 sec
Reputation Power: 13

Of course it does!

I use it for example automatically updating sums in bill header tables, automatically updating ticket status after events, automatically register into accounting after a bill/payment is made, etc.

July 8th, 2011, 01:17 PM

clivew

Contributing User

Join Date: Jan 2006

Location: Carlsbad, CA

Posts: 2,045

Time spent in forums: 1 Week 6 Days 2 h 37 m
Reputation Power: 382

Quote:

Originally Posted by nagysz

Surely not in the same table as the trigger!

Running a SELECT query is one thing, doing a modification is possibly going to lead you down some nasty recursive paths.

I would definitely advise against modifying a table from within one of its triggers.

Obviously I am talking about modifying via SQL NOT modifying the fields of the record being modified.

Clive

July 8th, 2011, 11:26 PM

nagysz

Contributing User

Join Date: Jul 2006

Posts: 200

Time spent in forums: 1 Day 11 h 19 m 28 sec
Reputation Power: 13

Quote:

Originally Posted by clivew

Sorry, i missed the "same table" part. I totally agree with the rest.

And yes, i have that scenario too. For example i store the menu of the client software in tree structure in a table, that has a PARENT field referencing the ID in the same table. And before any INSERT/UPDATE i have to check if i have a circular reference.

I paste here a quote from Helen Borrie's "The Firebird eBook":

Quote:

Self-Referencing Tables and Trees
Self-referencing tables that implement tree structures4 are a special case. Each row
in such a table is a node in a tree and inter-row dependencies are inherent. Any node
potentially has two “lives”: one as a parent to nodes beneath it, the other as a child to
a higher node. Triggers are likely to be required for all DML events, both to modify the
behavior of referential integrity constraints and to maintain the metatables (graphs)
used by some tree algorithms to make the state of the tree’s geometry available to
queries. Triggers for trees should always be designed with conditions and branches
that protect the structure from infinite loops.
Updating the Same Row
Never try to use an SQL statement to update or delete the same row that the trigger is
operating on. The following, for example, is not advisable:
CREATE TRIGGER O_SO_SILLY FOR ATABLE
BEFORE UPDATE
AS
BEGIN
UPDATE ATABLE SET ACOLUMN = NEW.ACOLUMN
WHERE ID = NEW.ID;
END ^
Always use the NEW variables for same-row modifications and never resolve an
exception by attempting to delete the row from within the trigger.

Chapter 31, Page 660

Last edited by nagysz : July 8th, 2011 at 11:38 PM.

Does Firebird allow SELECT in Trigger?

Developer Shed Advertisers and Affiliates