Oracle Trigger Subquery problem

Question

CREATE OR REPLACE TRIGGER "DISC_CLIENT"
BEFORE INSERT ON "PURCHASE"
FOR EACH ROW
DECLARE
checkclient PURCHASE.CLIENTNO%TYPE;
BEGIN
SELECT Clientno INTO checkclient
FROM PURCHASE
GROUP BY ClientNo
HAVING SUM(Amount)=(SELECT MAX(SUM(Amount)) FROM PURCHASE GROUP BY Clientno);
IF :new.ClientNo = checkclient
new.Amount := (:old.Amount * 0.90);
END IF;
END;
/

Seem to be having a problem with this trigger. I know there I cant use the WHEN() clause for subqueries so im hoping this would work but it doesnt! Ideas anyone? :/

Basically im trying to get this trigger to apply a discount to the amount value before inserting if the client matches the top client! : )

What exactly are you trying to achieve by this trigger? I would suppose that you want to give a 10% price discount to the client who spent the most money in your shop. But this trigger won't do it - trigger can't change :new and :old values and :old is empty for insert trigger. — Andrei LED
– Andrei LED, Commented Sep 22, 2011 at 18:01

Ben · Accepted Answer · 2011-09-22 20:34:16Z

There's a non-pretty but easy way round this, create a view and update that. You can then explicitly state all the columns in your trigger and put them in the table. You'd also be much better off creating a 1 row 2 column table, max_amount and then inserting the maximum amount and clientno into that each time. You should also really have a discounted amount column in the purchase table, as you ought to know who you've given discounts to. The amount charged is then amount - discount. This get's around both the mutating table and being unable to update :new.amount as well as making your queries much, much faster. As it stands you don't actually apply a discount if the current transaction is the highest, only if the client has placed the previous highest, so I've written it like that.

create or replace view purchase_view as
  select *
    from purchase;

CREATE OR REPLACE TRIGGER TR_PURCHASE_INSERT
  BEFORE INSERT ON PURCHASE_VIEW
  FOR EACH ROW

  DECLARE

  checkclient max_amount.clientno%type;
  checkamount max_amount.amount%type;
  discount    purchase.discount%type;

  BEGIN

    SELECT clientno, amount
      INTO checkclient, checkamount
      FROM max_amount;

   IF :new.clientno = checkclient then
      discount := 0.1 * :new.amount;
   ELSIF :new.amount > checkamount then
      update max_amount
         set clientno = :new.clientno
           , maxamount = :new.amount
             ; 
    END IF;

    -- Don-t specify columns so it breaks if you change
    -- the table and not the trigger
    insert into purchase 
    values ( :new.clientno
           , :new.amount
           , discount
           , :new.other_column );
END TR_PURCHASE_INSERT;
/

Andrei LED · Accepted Answer · 2011-09-22 17:46:00Z

1

As I remember a trigger can't select from a table it's fired for. Otherwise you'll get ORA-04091: table XXXX is mutating, trigger/function may not see it. Tom advises us not to put too much logic into triggers.

And if I understand your query, it should be like this:

SELECT Clientno INTO checkclient
FROM PURCHASE
GROUP BY ClientNo
HAVING SUM(Amount)=(select max (sum_amount) from (SELECT SUM(Amount) as sum_amount FROM PURCHASE GROUP BY Clientno));

This way it will return the client who spent the most money.

But I think it's better to do it this way:

select ClientNo
from (
  select ClientNo, sum (Amount) as sum_amount
  from PURCHASE
  group by ClientNo)
order by sum_amount
where rownum

edited Sep 22, 2011 at 17:46

answered Sep 22, 2011 at 17:39

Andrei LED

2,72920 silver badges25 bronze badges

Collectives™ on Stack Overflow

Oracle Trigger Subquery problem

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related