CODEWITHSUNDEEP
sqldatabasepostgresqltriggersplpgsql
ScumpinatoS
ScumpinatoS

Reputation: 13

How to use trigger in Postgres after Update?

Hi guys i need your help :D I'm using the latest version of PostgreSQL First of all, here is my database's tables:

CREATE TABLE colore (
  idcolore INTEGER PRIMARY KEY,
  nome VARCHAR(100),
  note TEXT
);

CREATE TABLE Prodotto (
    SKU varchar(50) PRIMARY KEY,
    nome varchar(255) NOT NULL,
    quantita INTEGER DEFAULT -1,
    idColore INTEGER,
    prezzo NUMERIC(10, 2),
    FOREIGN KEY(idColore) REFERENCES Colore(idColore) ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE Ordine (
    idOrdine INTEGER PRIMARY KEY,
    SKU varchar(50) NOT NULL,
    quantita INTEGER NOT NULL,
    CHECK (check_quantita(SKU, quantita)),
    FOREIGN KEY(SKU) REFERENCES Prodotto(SKU) ON UPDATE NO ACTION ON DELETE NO ACTION
);

What I want is that when I insert a new Ordine, the quantita of the Prodotto references by SKU is the quantity available minus the quantity ordered.

For Example:

I have this Prodotto:

SKU : AAA
Nome: Prodotto1
Quantita: 11

And then I do the following: INSERT INTO Ordine (idOrdine, SKU, quantita) VALUES (1, 'AAA', 10);

What I want is that after the last insert the quantity of the product AAA would be 1.

I've tried using this piece of code

CREATE OR REPLACE FUNCTION aggiorna_quantita() RETURNS trigger AS
$$
BEGIN
UPDATE Prodotto
    SET quantita = (SELECT Quantita FROM Prodotto WHERE SKU = TG_ARGV[0]) - TV_ARGV[$1]
    WHERE SKU = TV_ARGV[$0] ;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_aggiorna_quantita
AFTER INSERT ON Ordine
FOR EACH STATEMENT
EXECUTE PROCEDURE aggiorna_quantita(SKU, quantita);

But nothing happens :(

Thank you in advance and forgive me for my bad English :D

Upvotes: 1

Views: 2104

Answers (1)

Eelke
Eelke

Reputation: 21993

The arguments to a trigger can only be string literals. Simple names and numeric values are converted to strings at compile time. What you want cannot be done using these arguments. Luckily there is a much simpler method. Inside the trigger a variable called NEW is available which is the row that just got inserted.

Also you do not have to use a select to retrieve the current value of quantita.

Oh and don't use uppercase characters for object names in postgresql. It's handling of uppercase is very confusing because it converts them to lowercase unless you put the names between double quotes.

And you also want your trigger to be row level instead of statement level.

So your code would become:

CREATE OR REPLACE FUNCTION aggiorna_quantita() RETURNS trigger AS
$$
BEGIN
UPDATE prodotto
    SET quantita = prodotto.quantita - NEW.quantita
    WHERE sku = NEW.sku;
    RETURN NEW;
END
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_aggiorna_quantita
AFTER INSERT ON ordine
FOR EACH ROW
EXECUTE PROCEDURE aggiorna

Upvotes: 1

Related Questions