CODEWITHSUNDEEP
oracle-databaseplsqltriggerssubquery
AugustusWee
AugustusWee

Reputation: 67

Oracle Trigger Subquery problem

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! : )

Upvotes: 1

Views: 2643

Answers (2)

Ben
Ben

Reputation: 52863

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;
/

Upvotes: 2

Andrei LED
Andrei LED

Reputation: 2699

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

Upvotes: 1

Related Questions