Reputation: 57
I have the table Ticketsforsale:
Ticketsforsale(ticket_id, starting_date, price)
I need to create a trigger function which , in case of update on "starting_date" , will replace the old "starting_date" with the updated one in addition to lowering the "price" by 20% .
The trigger function I've came up with so far is the following :
create or replace function automatic_update()
RETURNS TRIGGER AS $$
begin
IF new.starting_date != ticketsforsale.starting_date
THEN old.starting_date = new.starting_date ,
ticketsforsale.price = ticketsforsale.price * (20/100) ;
END IF ;
RETURN NEW ;
END ;
$$
LANGUAGE plpgsql ;
CREATE TRIGGER automation
BEFORE INSERT OR UPDATE ON public.ticketsforsale
FOR EACH ROW EXECUTE PROCEDURE automatic_update()
The trigger function code is running properly without any errors.
But when I update the value of a "starting_date" column and try to save the change , I get the following error :
Missing FROM-clause entry on table 'ticketsforsale' LINE 1 :SELECT new.starting_date != ticketsforsale.starting_date * QUERY: SELECT new.starting_date != ticketsforsale.starting_date CONTEXT: PL/pgsql function automatic_update() line 3 at IF
I've tinkered with the function pretty much . However I didn't seem to get it right , thanks .
Upvotes: 1
Views: 1940
Reputation: 37527
You can use the old
pseudo record to access the values before the update. It doesn't make sense to set old
values though. And such a trigger makes no sense for INSERT
as there is no old value that can change here. And you're not lowering the price by 20% you set it to 20%.
Something like the following should work as you want.
CREATE OR REPLACE FUNCTION automatic_update()
RETURNS TRIGGER
AS
$$
BEGIN
IF new.starting_date <> old.starting_date THEN
new.price := old.price * .8;
END IF;
RETURN new;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER automation
BEFORE UPDATE
ON public.ticketsforsale
FOR EACH ROW
EXECUTE PROCEDURE automatic_update();
You could also move the condition to the triggers WHEN
.
CREATE OR REPLACE FUNCTION automatic_update()
RETURNS TRIGGER
AS
$$
BEGIN
new.price := old.price * .8;
RETURN new;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER automation
BEFORE UPDATE
ON public.ticketsforsale
FOR EACH ROW
WHEN (new.starting_date <> old.starting_date)
EXECUTE PROCEDURE automatic_update();
Upvotes: 2