| Subject | Re: [firebird-support] Re: Query a table within its trigger |
|---|---|
| Author | Helen Borrie |
| Post date | 2012-07-06T19:24:56Z |
At 10:18 PM 6/07/2012, hanszorn2000 wrote:
As an example (without knowing your rules) your BI/BU trigger on article could query LOCATION to get the location key and then do an EXISTS query into ARTICLE_LOCATION to determine whether that ARTICLE_LOCATION set already exists. The trigger then writes a row to ARTICLE_LOCATION or updates a row, according to what your rules require (possible using an INSERT OR UPDATE statement).
An analogy might be that, if you use a hammer to drive a screw into a panel of wood, you're likely to damage something: the screw or the wood or your thumb. Sometimes it will work and you won't damage anything, but you can't know what will happen until you actually do it. So, if it's important that both the wood and the screw are not damaged and you care about your thumb, you'll use a drill and a screwdriver.
./heLen
>--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:Unless I misread the purpose of the query, you seem to have this relationship flipped on its head. The LOCATION table should not hold a key to the article table. The article table should hold a key to the LOCATION table.
>>
>> If you have a row in a table that depends on values in other rows in the same table then you have a serious flaw in your relational design. Move that column or set into another table.
>
>That's an interesting point you are making. I have thought of that, but am not sure how to improve it. I can create a LOCATION table that holds all (possible) locations and the key to the article (and removing the location from the article table of course).
>Maybe that would be more 'relational', but it would not solve the problem that the ARTICLE table must be queried (in a join with LOCATION) inside the trigger. Or do you see another solution?The correct 'relational' way to solving this is to create an ARTICLE_LOCATION table that holds keys for both ARTICLE and LOCATION. It is called an "intersection table". Define the keys in such a way that you allow or disallow multiple occurrences of an article/location combination. By all means use triggers to implement and enforce your rules.
As an example (without knowing your rules) your BI/BU trigger on article could query LOCATION to get the location key and then do an EXISTS query into ARTICLE_LOCATION to determine whether that ARTICLE_LOCATION set already exists. The trigger then writes a row to ARTICLE_LOCATION or updates a row, according to what your rules require (possible using an INSERT OR UPDATE statement).
>And apart from this theoretical objection, is there a reason for Firebird to not correctly handle this?What does "correctly handle" mean? Proper abstraction of data is not peculiar to Firebird. Relational database systems are designed to "correctly handle" relationships between tables, not relationships between rows within a table. If you have inter-row dependencies in any relational database table, you have improper abstraction, which carries risk of unexpected results, even logical corruption.
An analogy might be that, if you use a hammer to drive a screw into a panel of wood, you're likely to damage something: the screw or the wood or your thumb. Sometimes it will work and you won't damage anything, but you can't know what will happen until you actually do it. So, if it's important that both the wood and the screw are not damaged and you care about your thumb, you'll use a drill and a screwdriver.
./heLen