Kreiranje trigera skripta

// Kreiranje funkcije za trigger

// INSERT
CREATE OR REPLACE FUNCTION noOfPassengers()
RETURNS TRIGGER AS
$$
BEGIN
NEW.no_of_passengers = (SELECT no_of_passengers FROM reservation
WHERE flight_id = NEW.flight_id AND flight_category_id = NEW.flight_category_id AND
client_id = NEW.client_id);
RETURN NEW;
END;

$$
LANGUAGE 'plpgsql';

// UPDATE
CREATE OR REPLACE FUNCTION reservationAfterUpdate()
RETURNS TRIGGER AS
$$
BEGIN
IF NEW.no_of_passengers <> OLD.no_of_passengers THEN
UPDATE extra_service
SET no_of_passengers = NEW.no_of_passengers
WHERE flight_id = NEW.flight_id AND flight_category_id =

NEW.flight_category_id AND client_id = NEW.client_id;
END IF;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

// EXCEPTION
CREATE OR REPLACE FUNCTION extraServiceAfterUpdate()
RETURNS TRIGGER AS
$$
BEGIN
IF NEW.flight_id <> OLD.flight_id OR NEW.flight_category_id <> OLD.flight_category_id
OR NEW.client_id <> OLD.client_id THEN

RAISE EXCEPTION 'You cannot update these columns';
END IF;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';

// Kreiranje triggera

CREATE TRIGGER after_reservation_update
AFTER UPDATE ON reservation
FOR EACH ROW
EXECUTE PROCEDURE reservationAfterUpdate();