6 Replies Latest reply: Nov 23, 2012 3:10 PM by rmespas80 RSS

    Cursors inside Triggers

    rmespas80
      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
          any/all SQL use cursor
          • 2. Re: Cursors inside Triggers
            Frank Kulash
            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
              rmespas80
              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
                >
                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
                  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
                    rmespas80
                    thanks for all your replys, I need a lot of reading to make