Forum Stats

  • 3,839,802 Users
  • 2,262,538 Discussions
  • 7,901,059 Comments

Discussions

PL/SQL Trigger Issue.

M.bro
M.bro Member Posts: 128 Blue Ribbon
edited Oct 31, 2018 1:15PM in SQL & PL/SQL

Hi All,

I am using 11g Database. I writing the trigger in 3 different tables.

Example Table Name : Table_TRG_1   ,  Table_TRG_2 , Table_TRG_3

Trigger Name : TRG_Table_TRG_1,  TRG_Table_TRG_2, TRG_Table_TRG_3

                                

Examples : Table_TRG_1   -- Insert new records in Table_TRG_1 trigger will fire and insert into the value to Table_TRG_2. (Trigger Name TRG_Table_TRG_1)

Table_TRG_2 --- If insert the new records in this table trigger will fire based on the condition update the few other fields.  (Trigger Name : TRG_Table_TRG_2).

Table_TRG_3 --- If some field will update in Table_TRG_2 then record should be deleted in Table_TRG_2 and move it Table_TRG_3.

I am mutating error will occurred. It should be table there is no view and there is like chain process (Automation  process.)

Note : Table_TRG_2 Some time insert the new or some time update the particular field then i will raised the trigger :TRG_Table_TRG_2

thanks & Regards

M.Bro.

Message was edited by: M.bro

Tagged:
clcarter

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Oct 31, 2018 11:22AM
    M.bro wrote:Hi All,I am using 11g Database. I writing trigger 3 tables. Examples : Table_TRG_1 -- Insert new records in Table_TRG_1 trigger will fire and insert into the value to Table_TRG_2. (Trigger Name TRG_Table_TRG_1)Table_TRG_2 --- If insert the new records in this table trigger will fire based on the condition update the few other fields. (Trigger Name : TRG_Table_TRG_2). Table_TRG_3 --- If some field will update in Table_TRG_2 then record should be deleted in Table_TRG_2 and move it Table_TRG_3.I am mutating error will occured. It should be table there is no view and there is like chain process (Automation process.)Note : Table_TRG_2 Some time insert the new or some time update the particular field then i will raised the trigger :TRG_Table_TRG_2 thanks & RegardsM.Bro.

    We can't say what is wrong since you decided to NOT show us the actual code being executed.

    Mutating table occurs when SQL is issued against same table upon which the trigger is based.

    Please click on URL below & respond accordingly

    Re: 2. How do I ask a question on the forums?

  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited Oct 31, 2018 11:27AM

    As John pointed the link, read it and follow the items especially #5 through #9.  We don't have your db environment nor objects nor data to test and give you feedback.

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,261 Red Diamond
    edited Oct 31, 2018 11:29AM

    Hi,

    Whenever you have a problem, please post a complete test script that the people who want to help you can re-create the problem and test their ideas.  Include the code for your triggers and a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
    Also post the exact results you want from that data, and an explanation of how you get those results from that data, with specific examples.
    If you're asking about a DML statement, such as INSERT, then the CREATE TABLE and INSERT statements you post should re-create the tables as they are before the DML, and the results  will be the contents of the changed table(s) when everything is finished.
    Simplify the problem as much as possible.  Remove all tables and columns that play no role in this problem.  Just make sure the simplified problem still causes the problem.

    Always say which version of Oracle you're using (for example, 12.2.0.1.0).
    See the Forum FAQ:

    Explain your business requirements.  Triggers may not be the best way to do what you need.  Materialized views can do a lot of things that may seem like jobs for triggers.  If you really do need triggers, consider INSTEAD OF triggers; they are often helpful in getting around mutating table problems.

  • Mark D Powell
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Oct 31, 2018 12:47PM

    M.bro, considering replacing the Table2 and Table3 triggers with a package called by the Table1 trigger that contains all the logic.  This will avoid the mutating table issue.  If the DML can be directly against Table2 and/or Table3 and you need the logic to be in effect you can replace the table triggers with triggers that just check the call stack for the caller and if the calling program is not the package fail the statement.  This will force use of the package to perform DML on these tables.

    - -

    This type of requirement raises the issue of it these three tables are properly normalized to begin with and if this specific design is the best way to deal with the business requirements.  Verifying the design should be the first step, but can be beyond the control of the DBA.

    - -

    HTH -- Mark D Powell --