CODEWITHSUNDEEP
sqltriggersdb2
Alladin
Alladin

Reputation: 1062

How to make a db2 trigger updates only the inserted row without going through all the records

I'm using IBM DB2 and I have the following trigger that updates a column in the Sales table after inserting in the same table

The PriceSize table:

create table PriceSize
(
  P_size varchar(20) primary key not null,
  P_Price decimal(5,2)
);

The CombosAndPromotions table:

Create table CombosAndPromotions
(
  CP_ID char(4) primary key not null,
  CP_Price decimal(5,2),
  CP_Type varchar (15),
  CP_Description long varchar
);

The sales table

create table sales(
    FID char(3) not null,
    CID int not null,
    PID char(3),
    P_size varchar(20),
    CP_ID char(4),
    Quantity int,
    Price Decimal(5,2) with default 0, 
    FOREIGN  key (FID) references Franchise,
    FOREIGN  key (CID) references Customer,
    FOREIGN  key (PID) references Product,
    FOREIGN  key (P_size) references PriceSize,
    FOREIGN  key (CP_ID) references CombosAndPromotions
    );

The trigger

create trigger calculate_Price
after insert on sales
referencing new as n
for each row mode db2sql

begin atomic
    if n.CP_ID is null then
      update Sales s
            set Price = (select Pricesize.P_price * s.Quantity
                       from Pricesize 
                       where s.P_size = Pricesize.P_size); 
    else 
      update Sales s
            set price = (select CombosAndPromotions.CP_price
            from CombosAndPromotions
                where s.CP_ID = CombosAndPromotions.CP_ID); 

    end if;
end@

but the issue is that this trigger updates all the rows not only the one inserted. I would like to get some help on how to make it affect only the row inserted. Thank you

Upvotes: 1

Views: 2918

Answers (1)

Lennart - Slava Ukraini
Lennart - Slava Ukraini

Reputation: 7171

Untested, but something like:

create trigger calculate_Price
before insert on sales
referencing new as n
for each row 
mode db2sql
    set price = case when n.CP_ID is null then
                    ( select ps.P_price * n.Quantity
                      from Pricesize ps
                      where n.P_size = ps.P_size )
                else
                    ( select cp.CP_price
                      from CombosAndPromotions cp
                      where n.CP_ID = cp.CP_ID )
                end @

In a before trigger you affect only the row that you are about to insert. Some test data:

insert into PriceSize (p_size, p_price) values ('a',10.0);
insert into CombosAndPromotions (CP_ID, cp_price) values ('b',20.0);
insert into sales (FID, CID, P_size, cp_id, quantity, price) values ('x',1,'a',null,5,100);
insert into sales (FID, CID, P_size, cp_id, quantity, price) values ('y',2,null,'b',5,100);

select * from sales;
FID CID         PID P_SIZE               CP_ID QUANTITY    PRICE  
--- ----------- --- -------------------- ----- ----------- -------
x             1 -   a                    -               5   50.00
y             2 -   -                    b               5   20.00

2 record(s) selected.

for FID=X cp_id is null so the price = 10 * 5 = 50
for FID=y cp_id is not null so the price = 20

correct?

Upvotes: 4

Related Questions