#1
  1. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383

    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.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2007
    Posts
    36
    Rep Power
    8
    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.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Thanks.
    I will give it a try.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    14
    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.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,055
    Rep Power
    383
    Originally Posted by nagysz
    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.
    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
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    14
    Originally Posted by clivew
    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


    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":
    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.

IMN logo majestic logo threadwatch logo seochat tools logo