Home » SQL & PL/SQL » SQL & PL/SQL » How to use the Loop In a Trigger (Oracle 9i and Windows 2003)
How to use the Loop In a Trigger [message #354362] Sat, 18 October 2008 02:23 Go to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Hello, I have got the following trigger code:
CREATE OR REPLACE TRIGGER DO_DCMBL.ACHIEVEMENT_TR
AFTER INSERT
ON DO_DCMBL.MASTER_SALES_TBL 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    UPDATE Do_Dcmbl.Dealer_Achievement_Tbl
     SET Achieved_Qty_Monthly =  nvl(Achieved_Qty_Monthly,0) + :New.product_quantity, 
Achieved_Amt_Monthly =  nvl(Achieved_Amt_Monthly,0) + :New.Sales_amount
      WHERE  Dealer_Achievement_Tbl.product_Code =:New.product_Code AND
 Dealer_Achievement_Tbl.DISTRIBUTOR_CODE=:New.DISTRIBUTOR_CODE AND
 Dealer_Achievement_Tbl.MONTH_ID=:New.MONTH_ID AND
 Dealer_Achievement_Tbl.TARGET_YEAR=:New.YEAR;

  END IF;

END;


Now the following trigger work nice if in the Dealer_archivement_tbl has got only one row, but couldn't work if there is any multiple row. So now i would like to use a loop to verify if there is anymore row to update or not, but can't start. So anyone please suggest me or give me some hints so that i can proceed with the problem to solved it out. In the figure below as you can see the trigger worked for one row but do nothing for the second row.
  • Attachment: Table.jpg
    (Size: 20.88KB, Downloaded 77 times)

[Updated on: Sat, 18 October 2008 02:28] by Moderator

Report message to a moderator

Re: How to use the Loop In a Trigger [message #354363 is a reply to message #354362] Sat, 18 October 2008 02:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't do that in a trigger do it in a procedure.
And this will never work in multi-user environment unless you lock the table, one more reason to NOT do it in a trigger.

Regards
Michel
Re: How to use the Loop In a Trigger [message #354364 is a reply to message #354362] Sat, 18 October 2008 02:35 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks for the reply Michel. Now if i do it in the procedure the same code i should use or something else having a loop or other conditional expression? Will you please guide me on that matter.
Re: How to use the Loop In a Trigger [message #354369 is a reply to message #354364] Sat, 18 October 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't explain what is your business need so I can't answer.

Regards
Michel
Re: How to use the Loop In a Trigger [message #354413 is a reply to message #354362] Sat, 18 October 2008 12:57 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
The update statement is capable of updating multiple rows that meet the provided criteria. The second row was not updated because it had a different product_code than the first row and you specified that the product_code must match as part of the criteria in the when clause of your update statement.
Re: How to use the Loop In a Trigger [message #354416 is a reply to message #354362] Sat, 18 October 2008 13:09 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
This is something that has always confused me over and over. Can someone please provide additional information and examples as to why this particualte update inside a trigger would be a problem in a multiuser environment but the correspoding update in a plsql routine won't be a problem?

I never got this.

Kevin
Here is the OP original trigger code for easier reference:
CREATE OR REPLACE TRIGGER Do_dcmbl.Achievement_tr
    AFTER INSERT ON Do_dcmbl.Master_Sales_Tbl
    FOR EACH ROW
    BEGIN
      IF InsertIng THEN
        UPDATE Do_dcmbl.Dealer_Achievement_Tbl
        SET    Achieved_qty_Monthly = Nvl(Achieved_qty_Monthly,0) + :New.Product_Quantity,
               Achieved_Amt_Monthly = Nvl(Achieved_Amt_Monthly,0) + :New.Sales_Amount
        WHERE  Dealer_Achievement_Tbl.Product_Code = :New.Product_Code
               AND Dealer_Achievement_Tbl.Distributor_Code = :New.Distributor_Code
               AND Dealer_Achievement_Tbl.Month_Id = :New.Month_Id
               AND Dealer_Achievement_Tbl.Target_Year = :New.YEAR;
      END IF;
    END;
Re: How to use the Loop In a Trigger [message #354422 is a reply to message #354416] Sat, 18 October 2008 15:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can someone please provide additional information and examples as to why this particualte update inside a trigger would be a problem in a multiuser environment but the correspoding update in a plsql routine won't be a problem?

This is not what I said.
There are 2 parts:
1/ Complex logic must be put in procedure and not in trigger
2/ When there are several statements, you must lock the table/rows to support multiuser environment (and in this case the updated row(s) should be lock BEFORE the insert). It does not matter this is in a trigger or a procedure.
(In my answer "this" refers to the code not to the trigger.)

Regards
Michel
Re: How to use the Loop In a Trigger [message #354424 is a reply to message #354422] Sat, 18 October 2008 16:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
In a multi-user environment with such a trigger, Oracle will automatically lock until commit, causing subsequent users to wait. Upon closer examination of the total problem, all of the information in dealer_achievement_tbl can be selected from master_sales_tbl. So, when the information is desired, it should simply be selected or, at most, a view should be used. Maintaining a separate table through a trigger or procedure constitutes unnecessary duplicate storage and locking and is therefore generally a bad practice.
Re: How to use the Loop In a Trigger [message #354425 is a reply to message #354362] Sat, 18 October 2008 17:03 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Wow, Great to have such a wonderful conversation from all of you. Now as i have got different product code in the 2nd row now what to do in order to update the 2nd row as well. It may happen that there could be more than 7-10 different product and need to updated if any insertion is take place in the master Sales Table. Please seeking your help.I am just end of the system to be build and can't move ahead because of that problem. As usual please try to help me. I will be grateful to all of you.

Thanks Mahatab
Re: How to use the Loop In a Trigger [message #354427 is a reply to message #354362] Sat, 18 October 2008 19:05 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Now as i have got different product code in the 2nd row now what to do in order to update the 2nd row as well.

Maybe I just did not read the thread carefully, but I miss one point in the question: to what values shall be the other rows updated and how are they derived from the inserted row?

I can imagine just two scenarios:
1) they shall be the same as in the first row - then removal join on PRODUCT_CODE from the WHERE clause will fill them
2) they shall be taken from rows with other product codes - then it is not necessary to update them as the inserted row does not change them
Re: How to use the Loop In a Trigger [message #354428 is a reply to message #354362] Sat, 18 October 2008 21:22 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Another thought: If the business requirements are such (and only the O.P. can answer this) that the dealer achievement table must be maintained in a certain relationship with the master sales table, then I urge the O.P. not to try to reinvent the wheel with triggers and research Materialized Views. It sounds like you really have a complex replication problem. I tried once (in the 7.3 days) to implement one with triggers.

The Oracle manual said such a task would not work.

It didn't work.

And I never could quite figure out why it wasn't working.

But this won't happen again, because Oracle has already solved all the problems.
Re: How to use the Loop In a Trigger [message #354436 is a reply to message #354362] Sat, 18 October 2008 22:43 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Here is the Business Logic:

1. I have got a master Sales Table where Delivery order is stored. In this table i have got several fields but among those fields there are two fields namely, Product Quantity and sales Amount are the main field i would like to stored in the Dealer Achievement Table.

2. Whenever an entry will be inserted into Master Sales Table then only the product quantity and sales amount will be inserted in the Dealer achievement table.

3. In the dealer achievement table the same product code and its quantity will be updated while a new insertion will be done to the master sales table having the same product code.


Now hope the logic is clear and please help me if you can.
Re: How to use the Loop In a Trigger [message #354451 is a reply to message #354362] Sun, 19 October 2008 05:12 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Have a look at MERGE statement.
Documentation link: http://download.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_915a.htm#SQLRF01606

But you shall better study the concept of materialized views, as suggested by TheSingerman, and implement it that way.
Documentation link: http://download.oracle.com/docs/cd/B10501_01/server.920/a96524/c11schem.htm#CNCPT411
Re: How to use the Loop In a Trigger [message #354455 is a reply to message #354362] Sun, 19 October 2008 06:04 Go to previous messageGo to next message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks for the reply, according to your reply i have studied both the link but couldn't find the resource i need. i have tried with merging but yet no result found so far. I guess any one of you may have the same problem to solve while designing your system. So at least can any one provide me a piece of code to work with which shows how to update multiple rows.
Re: How to use the Loop In a Trigger [message #354457 is a reply to message #354455] Sun, 19 October 2008 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you read (and understand) Barbara's answer?
Quote:
So, when the information is desired, it should simply be selected or, at most, a view should be used. Maintaining a separate table through a trigger or procedure constitutes unnecessary duplicate storage and locking and is therefore generally a bad practice.


Regards
Michel
Re: How to use the Loop In a Trigger [message #354480 is a reply to message #354455] Sun, 19 October 2008 12:35 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8631
Registered: November 2002
Location: California, USA
Senior Member
There are various ways such a business requirement could be handled:

1. You could maintain a separate dealer_achievement_tbl and use either a procedure or a trigger so that each time you insert a new row into the master_sales_tbl, the single corresponding row in the dealer_achievement_tbl is updated. This requires that the trigger exist prior to inserting any rows into the master_sales_tbl. You keep repeatedly expecting a trigger that is only supposed to update one row to update other rows and ignoring questions about what you would expect that row to be updated to and Flyboy's explanation of how different scenarios should be handled. This is not the best method.

2. You could maintain a separate dealer_achievement_tbl and use an update statement or a merge statement to update all of the rows in that table prior to selecting from it. This is not the best method.

3. You could simply select from the master_sales_tbl the information you need, without maintaining a separate dealer_achievement_tbl, without using a trigger, without using a procedure, without using an update statment, without using a merge statement. For convenience and efficiency you could create a view or materialized view that uses that select statement. This is the best method. I have provided a simple demonstration below, using a view. Notice how I have provided create table and insert statements, which is what you should have included in your original post, as per the forum guidelines. I have provided only partial code for the select statement in the view, so that you can figure that much out. This is not a complex business problem. This is a simple beginner's problem involving a select with SUM and GROUP BY that you have turned into an unnecessarily complicated mess by providing incomplete mismatched information and ignoring questions that were asked in order to get clarification, causing people to offer various suggestions based on their best guess as to what you were trying to accomplish. What I have provided may not exactly match what you have, but it should be close enough to demonstrate the concept.

-- tables and sample data that you should have provided:
SCOTT@orcl_11g> CREATE TABLE master_sales_tbl
  2    (distributor_code      VARCHAR2 (16),
  3  	year		      NUMBER,
  4  	month_id	      NUMBER,
  5  	product_code	      VARCHAR2 (12),
  6  	product_quantity      NUMBER,
  7  	sales_amount	      NUMBER)
  8  /

Table created.

SCOTT@orcl_11g> INSERT INTO master_sales_tbl (distributor_code, year, month_id, product_code, product_quantity, sales_amount)
  2  VALUES ('Distributor1', 2008, 10, 'Product1', 40, 8259.2)
  3  /

1 row created.

SCOTT@orcl_11g> INSERT INTO master_sales_tbl (distributor_code, year, month_id, product_code, product_quantity, sales_amount)
  2  VALUES ('Distributor1', 2008, 10, 'Product2', 2, 20)
  3  /

1 row created.

SCOTT@orcl_11g> INSERT INTO master_sales_tbl (distributor_code, year, month_id, product_code, product_quantity, sales_amount)
  2  VALUES ('Distributor1', 2008, 10, 'Product2', 8, 80)
  3  /

1 row created.

SCOTT@orcl_11g> SELECT * FROM master_sales_tbl
  2  /

DISTRIBUTOR_CODE       YEAR   MONTH_ID PRODUCT_CODE PRODUCT_QUANTITY SALES_AMOUNT
---------------- ---------- ---------- ------------ ---------------- ------------
Distributor1           2008         10 Product1                   40       8259.2
Distributor1           2008         10 Product2                    2           20
Distributor1           2008         10 Product2                    8           80

SCOTT@orcl_11g>


-- creation of view with partial code:
SCOTT@orcl_11g> CREATE OR REPLACE VIEW Dealer_Achievement_View AS
  2  SELECT ...,
  3  	    ...,
  4  	    ...,
  5  	    ...,
  6  	    SUM (...) ...,
  7  	    SUM (...) ...
  8  FROM   master_sales_tbl
  9  GROUP  BY ..., ..., ..., ...
 10  /

View created.


-- select from view:
SCOTT@orcl_11g> SELECT * FROM dealer_achievement_view
  2  /

DISTRIBUTOR_CODE TARGET_YEAR   MONTH_ID PRODUCT_CODE ACHIEVED_QTY_MONTHLY ACHIEVED_AMT_MONTHLY
---------------- ----------- ---------- ------------ -------------------- --------------------
Distributor1            2008         10 Product1                       40               8259.2
Distributor1            2008         10 Product2                       10                  100

SCOTT@orcl_11g>

Re: How to use the Loop In a Trigger [message #354483 is a reply to message #354362] Sun, 19 October 2008 15:32 Go to previous message
mahatab
Messages: 98
Registered: January 2008
Location: Dhaka
Member

Thanks a lot, it was beyond my expectation, means the amount of time you have spent for my problem. I am really grateful to you and all other members of this forum. I will try working with your concept you have provided and also let you know my update. Thanks once again.
Previous Topic: Pl Sql - Ref Cursorsor
Next Topic: Remote data writing with Stored Procedure
Goto Forum:
  


Current Time: Mon Dec 05 15:11:16 CST 2016

Total time taken to generate the page: 0.13058 seconds