create trigger in postgreSQL

Question

I am new to PostgreSQL and I am migrating to it from MySQL.Trying to creating Trigger in that.But can't done with it.

This is my MySQL Query.

  delimiter $$
  create trigger delVPNProfile before delete on VPN_Profile_List for each row begin update userinfo set Profile_ID='-1' where Profile_ID=old.Profile_ID; end;$$
  delimiter ;

My PostgreSQL Query is like this.

  $ CREATE FUNCTION make_default_privileges() returns trigger as $$
  begin
  update userinfo set Profile_ID='-1' where Profile_ID=old.Profile_ID;
  end;
  $$ language plpgsql;
  create trigger delVPNProfile before delete on VPN_Profile_List for each row begin EXECUTE PROCEDURE make_default_privileges();

What specifically is wrong with your PostgreSQL trigger? Syntax errors? Doesn't do what you expect it to? Something else? — mu is too short
– mu is too short, Commented Feb 29, 2016 at 7:01
What is the error message shown? Please provide some more details. — Vijay
– Vijay, Commented Feb 29, 2016 at 7:07
One thing I noticed, there is no need to include "begin" in trigger creation syntax. It could be just written as, "..for each row EXECUTE PROCEDURE make_default_privileges();". — Vijay
– Vijay, Commented Feb 29, 2016 at 7:13
@muistooshort It doesn't show any error messages.But when I check for crated triggers it doesn't shows any. — snirali
– snirali, Commented Feb 29, 2016 at 7:19
@Vijay same doesn't work.I am checking the available triggers with select * from pg_trigger; is it right? — snirali
– snirali, Commented Feb 29, 2016 at 7:24

Mesbah Gueffaf · Accepted Answer · 2016-02-29 11:07:56Z

1

CREATE FUNCTION make_default_privileges() RETURNS trigger AS      $make_default_privileges$
old_id          integer;
BEGIN
old_id=OLD.Profile_ID;
update userinfo set Profile_ID='-1' where Profile_ID=old_id;
RETURN OLD;
END;
   $make_default_privileges$ LANGUAGE plpgsql;

CREATE TRIGGER delVPNProfile before delete on VPN_Profile_List
FOR EACH ROW EXECUTE PROCEDURE make_default_privileges();

answered Feb 29, 2016 at 11:07

Mesbah Gueffaf

5581 gold badge7 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

create trigger in postgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related