Here are some tables in PostgreSQL that I have created:
CREATE TABLE Card(card_id INT PRIMARY KEY, monthly_deduction DOUBLE);
CREATE TABLE Customer(costumer_ID INT PRIMARY KEY, card_number INT, amount DOUBLE, FOREIGN KEY (card_number) references Card(card_ID));
CREATE TABLE Ride(ride_ID INT PRIMARY KEY, rider_ID INT, FOREIGN KEY (rider_ID) REFERENCES Customer(costumer_ID))
I want to create a trigger which, every time a row is inserted in the Ride table, it decrements the monthly_deduction value of a row in Card table by 2. I tried to find a solution online about triggers, but none of them explains how to change a value of a certain column. Please, answer me as soon as possible. Thank you for your attention.
All you have to do is run an UPDATE
statement in the trigger:
UPDATE card
SET monthly_deduction = monthly_deduction - 2
FROM customer
WHERE customer.card_number = card.card_id
AND customer.customer_id = NEW.rider_id;