PostgreSQL Trigger – Rewrite from SQLite

postgresqlsqlitetrigger

Can you help me please with this problem? I need rewrite this simply trigger from sqlite to PostgreSQL format:

In sqlite:

CREATE TRIGGER connections_INSERT_update_connection_root_trg
                    AFTER INSERT ON dionaea.connections
                    FOR EACH ROW
                    WHEN
                            new.connection_root IS NULL
                    BEGIN
                            UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
                    END;

I have tried this:

CREATE FUNCTION dionaea.connections_INSERT_update_connection_root_trg() RETURNS TRIGGER AS '
                    BEGIN
                            UPDATE connections SET connection_root = connection WHERE connection = new.connection AND new.connection_root IS NULL;
                    END
                    ' LANGUAGE plpgsql;

It is correctly accepted.

CREATE TRIGGER connections_INSERT_update_connection_root_trg
AFTER INSERT ON dionaea.connections
FOR EACH ROW
WHEN
 NEW.connection_root IS NULL
EXECUTE PROCEDURE dionaea.connections_INSERT_update_connection_root_trg();

After this I get this error: 

NEW.connection_root IS NULL

Where is the problem please?

Best Answer

Need parentheses in the WHEN part:

CREATE TRIGGER connections_INSERT_update_connection_root_trg
AFTER INSERT ON dionaea.connections
FOR EACH ROW
WHEN
 (NEW.connection_root IS NULL)
EXECUTE PROCEDURE dionaea.connections_INSERT_update_connection_root_trg();

You also need to return NEW in your trigger function. 

create or replace function  dionaea.connections_INSERT_update_connection_root_trg() returns trigger language plpgsql as $$
begin
  update dionaea.connections set connection_root = connection where connection = new.connection and new.connection_root is null;
  return new;
end $$;
Related Question