Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: to TRIGGER or not to TRIGGER

    On several Oracle forums, I've seen several people take a strong position on avoiding the use of TRIGGERs for business rules. Could we start a thread on why people are for/against them (unless someone has a link to one that's already exhausted this topic).

    I've been pushing the use of TRIGGERs for business rules, so that we don't have to code the equivalent in any of our multiple interfaces (desktop and web), as well as having the business rules remain intact if we're changing the data via TOAD. There's an "escape" at the top of each trigger as well, where a PACKAGE spec variable is tested for a particular value, allowing the the remainder of the TRIGGER code to be aborted if necessary.

    --=cf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I'm happy to use triggers for some things, such as:
    • Writing audit logs
    • Maintaining "last updated" username and timestamp columns
    • Forcing uppercase on text columns
    • Trimming time portion off date values

    I'll occasionally use them for validation where a constraint cannot be used, but there are issues with this that mean I wouldn't do it in general:
    • Mutating tables: these are a PITA to code around correctly, and easy to code around in a manner that corrupts the database ("just add pragma autonomous_transaction"!)
    • Complexity: may have some rules to invoke before the DML and some after; actions may fire triggers on other tables etc.

    In such cases my preference is for an API layer that controls what can be done and no grants that enable users or applications to insert/update data directly.

Posting Permissions

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