Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Disadvantages of triggers

    A colleague remarked to me today that she does not care for triggers, but did elaborate on the reason. I would welcome any comments from the group regarding inherent disadvantages in the use of a properly designed trigger. I am aware that there is the risk of cascading if one is not careful, but are there any other issues that merit the avoidance of the use of triggers?

    thank you
    tjguillo

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There is that irritating "table is mutating" issue, which many developers "fix" by using autonomous_transaction, which is wrong and causes data corruption.

  3. #3
    Join Date
    Jun 2004
    Posts
    3

    triggers

    I think it gets down to design and how you use the triggers. Triggers, by themselves, are not a bad thing. At Ixion we provide the option to use "trigger-based data capture" for our data replication solution, IxPropagator. We have struggled with the "perception" that the triggers might add unnecessary overhead for our clients' already-over-taxed high-volume apps. But the reality is our triggers add no more overhead than our (or anyone elses) log-based data capture, and we prove this in the lab. So unless our clients are wanting to replicate SAP or PeopleSoft, we always encourage the use of the triggers!

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Triggers may be very useful in some situations, but it really depends on the situation. IMHO, I'd say one can use them, but always carefully and with parcimony : don't overuse triggers. Despite of what you say, atknudsen, triggers always add overhead (more or less). So, for example, if you've got a table on which you want to perform lightspeed inserts, DON'T create a trigger which will be fired on each insert in this table.

    BTW, Tony, what do you mean by the "table is mutating" issue ?

    Regards,

    RBARAER

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by RBARAER
    BTW, Tony, what do you mean by the "table is mutating" issue ?
    I'm surprised you haven't come across that before!

    See Avoiding Mutating Tables.

    Once triggers move beyond the simplest of processing like computing defaults and auditing statements, etc., then they tend to get tricky. Mutating tables is one issue. Another is that there can be many triggers that fire together, and the order of firing is not guaranteed. This one looks safe enough:

    Code:
    CREATE TRIGGER trg1 BEFORE INSERT ON emp
    FOR EACH ROW
    BEGIN
      IF :NEW.hiredate > SYSDATE THEN
        RAISE_APPLICATION_ERROR(-20001,'Hire date cannot be in the future');
      END IF;
    END;
    /
    What could possibly go wrong? Try adding this trigger:

    Code:
    CREATE TRIGGER trg2 BEFORE INSERT ON emp
    FOR EACH ROW
    BEGIN
      IF :NEW.hiredate IS NULL THEN
        :NEW.hiredate := SYSDATE+1;
      END IF;
    END;
    /
    Now do this:

    INSERT INTO emp (empno) values (123);

    What happens?

    If trg2 fires before trg1 then:
    1) hiredate gets set to tomorrow's date
    2) record is rejected because 'Hire date cannot be in the future'

    If trg1 fires before trg2 then:
    1) record is not rejected, because NULL hiredate isn't in the future
    2) hiredate gets set to tomorrow's date

    Now this is a trivial and perhaps silly example, and of course it can be solved (trg1 should be an AFTER trigger). But it serves to illustrate that designing triggers isn't child's play. There are many traps for the unwary.

    I have recently seen a system where each table had several triggers, each of which performed inserts or updates of other tables, which in turn had several triggers, ... Trying to figure out what would happen if you inserted a row into a table involved drawing a big messy diagram with arrows going all over the place!

    I agree with your position: I certainly do use triggers, but not to excess. If processing is very complex I might prefer to wrap the logic in a packaged procedure (API) and not grant direct DML on the table to users.

  6. #6
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I'm surprised you haven't come across that before!
    Well, you know, I don't have your experience, and I've still got many many things to learn. I learnt DB Conception / SQL on Oracle at my engineer school, and it's been about 10 months now that I develop on Oracle, as well as learn about tuning/administering an Oracle db (I couldn't say how many thousands of Oracle doc pages I've read and re-read during these 10 months ! ). I learn fast, but there is still a long way to go since I come to half of your skills and knowledge . Anyway, thank you very much for the answer and the article, as well as for all the good work you're doing in this forum.

    Best Regards,

    RBARAER

  7. #7
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Triggers: I once worked with a developer on a banking contract. He believed that writing any Stored Procedure was a failure on his part. He believed that everything could be done in triggers.

    Luckily it only took the bank 12 months to work out that this was not right.

    I don't know where he is today, but he's probably still telling the world that triggers are the answer. Sound familiar.. these extreme, catch all phrases?

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •