This discussion is archived
6 Replies Latest reply: Nov 23, 2012 1:10 PM by 767682 RSS

Cursors inside Triggers

767682 Newbie
Currently Being Moderated
hello,

I want to perform an analysis of a database, but I have some questions about things that are more desirable or not. for this I would like to make some questions and see if I can help to clarify my doubts:

Problems that have to use cursors in triggers.

Affects the performance of the database?

To what extent is advisable to use triggers?

thank you very much

appreciate your help

RON
  • 1. Re: Cursors inside Triggers
    sb92075 Guru
    Currently Being Moderated
    any/all SQL use cursor
  • 2. Re: Cursors inside Triggers
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Ron,
    rmespas80 wrote:
    hello,

    I want to perform an analysis of a database, but I have some questions about things that are more desirable or not. for this I would like to make some questions and see if I can help to clarify my doubts:

    Problems that have to use cursors in triggers.

    Affects the performance of the database?
    Yes, of course. Anything you do in the database affects its performance.
    Doing something in a trigger doesn't affect the database performance any more or less than doing the same thing outside of a trigger.
    To what extent is advisable to use triggers?
    Use triggers as little as possible.
    If you can do what you want either with or without a trigger, the way without the trigger is likely to be simpler,more efficient and more robust than the way with the trigger.
  • 3. Re: Cursors inside Triggers
    767682 Newbie
    Currently Being Moderated
    thanks frank

    this was my question, since in several tables have something like the following:

    12 triggers for table

    •     BIR     Before Insert Row
    •     BDR     Before Delete Row
    •     BUR     Before Update Row
    •     BIS     Before Insert Statement
    •     AIR     After Insert Row
    •     AIS     After Insert Statement
    •     BUS     Before Update Statement
    •     AUR     After Update Row
    •     AUS     After Update Statement
    •     BDS     Before Delete Statement
    •     ADR     After Delete Row
    •     ADS     After Delete Statement

    triggers execute in various validations, calling packages and other queries.

    above the triggers were created with an application to manage business rules and audit, all the logic is in the database. This application was who default towards creating triggers are used or not.

    I need to demonstrate that the triggers affect the development and performance of a Web application
  • 4. Re: Cursors inside Triggers
    rp0428 Guru
    Currently Being Moderated
    >
    I want to perform an analysis of a database, but I have some questions about things that are more desirable or not. for this I would like to make some questions and see if I can help to clarify my doubts:

    Problems that have to use cursors in triggers.
    >
    Nonsense! Who said you have to use cursors in triggers to do what you want? What kind of triggers are you talking about?

    If you mean triggers on tables then you definitely are using the wrong method.

    Trigger code is always non-transactional; the transaction that causes the trigger to fire could issue a ROLLBACK. That means that it 'never happened'. If you do something in the trigger that performs actions that can't be rolled back (send an email, call an anonymous transaction procedure, etc) then that action will still get done even though the original action was rolled back.

    Also, some triggers (e.g. before row triggers) can fire multple times for the same event. That can cause other code you put in the trigger to fire multple times which is usually not desireable.

    Why don't you explain what you mean by 'perform an analysis of a database' so we can suggest some alternative ways to do this.

    And you need to post your 4 digit Oracle version whenever you post.
  • 5. Re: Cursors inside Triggers
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    rmespas80 wrote:
    thanks frank

    this was my question, since in several tables have something like the following:

    12 triggers for table
    That's a lot of triggers! Do you really need all of them?
    •     BIR     Before Insert Row
    •     BDR     Before Delete Row
    •     BUR     Before Update Row
    •     BIS     Before Insert Statement
    •     AIR     After Insert Row
    •     AIS     After Insert Statement
    •     BUS     Before Update Statement
    •     AUR     After Update Row
    •     AUS     After Update Statement
    •     BDS     Before Delete Statement
    •     ADR     After Delete Row
    •     ADS     After Delete Statement

    triggers execute in various validations, calling packages and other queries.

    above the triggers were created with an application to manage business rules and audit, all the logic is in the database.
    Oracle provides some built-in auditing, which is more reliable than, and almost certain to be more efficient than, any user-defined triggers to do the same thing.
    This application was who default towards creating triggers are used or not.
    Sorry, I don't understand this last sentence at all.
    I need to demonstrate that the triggers affect the development and performance of a Web application
    What do you mean by "affect the development"?

    As for performance, do some tests with and without the triggers.
    If it's difficult (or impossible) to disable the triggers for testing, then make copies of the tables without the triggers. Make sure there's no row chaining in the original tables, then copy the tables (including the rows, indexes and constraints) and gather statistics.
  • 6. Re: Cursors inside Triggers
    767682 Newbie
    Currently Being Moderated
    thanks for all your replys, I need a lot of reading to make

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points