I'm working with DB2 and I have to make a trigger that after a certain update on 'Disponibilita' has to do two differentes operation with the table 'Promozioni' here the schemas:

create table PROMOZIONI (
     PID char(5) not null primary key,
     Valore DEC(4,2) not null,
     NumProdotti INT not null DEFAULT 0 );

create table DISPONIBILITA (
     CodProdotto char(5) not null,
     CodNegozio char(5) not null,
     Quantita INT not null,
     PID char(5) references PROMOZIONI,
     primary key (CodProdotto, CodNegozio));

and this is the trigger that obviously doesn't work:

Create or replace trigger AggiornaNumProdotti
After Update on Disponibilita 
referencing old as O new as N
for each row
update Promozioni p
SET NumProdotti=NumProdotti 1
Where N.PID is not null and N.PID=p.PID;
UPDATE Promozioni p2
SET NumProdotti=NumProdotti-1
WHERE O.PID is not null and O.PID=p2.PID;

is there any way to make a single trigger or i'm force to create two differentes ones for each specific instruction? Thanks a lot

CodePudding user response：

For more than one query you need a BEGIN and END

create table PROMOZIONI (
     PID char(5) not null primary key,
     Valore DEC(4,2) not null,
     NumProdotti INT not null DEFAULT 0 );

INSERT INTO PROMOZIONI VALUES ('1',1.2,0),
('2',1.2,0)

create table DISPONIBILITA (
     CodProdotto char(5) not null,
     CodNegozio char(5) not null,
     Quantita INT not null,
     PID char(5) references PROMOZIONI,
     primary key (CodProdotto, CodNegozio));

INSERT INTO DISPONIBILITA VALUES ('1','1',1,'1')

Create or replace trigger AggiornaNumProdotti
After Update on Disponibilita 
referencing old as O new as N
for each row
  BEGIN
update Promozioni p
SET NumProdotti=NumProdotti 1
Where N.PID is not null and N.PID=p.PID;
UPDATE Promozioni p2
SET NumProdotti=NumProdotti-1
WHERE O.PID is not null and O.PID=p2.PID;
END;

UPDATE  DISPONIBILITA SET PID = '2' WHERE PID = '1'

SELECT * FROM PROMOZIONI

fiddle