CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
Nektarios Papagewrgiou
Nektarios Papagewrgiou

Reputation: 57

Trigger function that Updates some columns after update of a specific column

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

Answers (1)

sticky bit
sticky bit

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

Related Questions