Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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 $$;
share|improve this answer
    
Thank you very much. –  user41060 Jun 14 at 22:52

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.