Error writing trigger script on PostgreSQL

Question

I have a SQL script for triggers. It was automatically generated by a great software for database modelling called "Win'Design".

So, after I created the database with all the tables (0 data) I wanted to copy/paste the script on the pgAdmin query editor.

Here's an extract:

-- Trigger de modification ----------------------------------------------
CREATE TRIGGER TU_SOURCE_OPEN_DATA
AFTER UPDATE ON SOURCE_OPEN_DATA FOR EACH ROW
EXECUTE PROCEDURE 
     -- Interdire la modification de la clé étrangère référençant la table 
     -- SOURCE_PARAMETRE.

     if
          :OLD.ID_SOURCE_PARAMETRE <> :NEW.ID_SOURCE_PARAMETRE
     then
               raise_application_error(
               -20008,
               'Modification de la clé étrangère référençant "SOURCE_PARAMETRE" interdite.');
     end if;

/

DROP TRIGGER TI_SOURCE_OPEN_DATA;

-- Trigger d'insertion ----------------------------------------------
CREATE TRIGGER TI_SOURCE_OPEN_DATA
AFTER INSERT ON SOURCE_OPEN_DATA FOR EACH ROW
EXECUTE PROCEDURE 
     -- Sauf valeur nulle autorisée, interdire la création d'une occurrence de SOURCE_OPEN_DATA 
     -- s'il n'existe pas d'occurrence correspondante dans la table SOURCE_PARAMETRE.

     select count(*) into numrows
     from SOURCE_PARAMETRE
     where
          :NEW.ID_SOURCE_PARAMETRE = SOURCE_PARAMETRE.ID_SOURCE_PARAMETRE;
     if 
          (
          numrows = 0 
          )
     then
          raise_application_error(
               -20002,
               'Impossible d''ajouter "SOURCE_OPEN_DATA" car "SOURCE_PARAMETRE" n''existe pas.');
     end if;

/



-- ------------------------------------------------------------------------------- 
--   Table : SECTEUR
-- ------------------------------------------------------------------------------- 

DROP TRIGGER TD_SECTEUR;

-- Trigger de suppression ----------------------------------------------
CREATE TRIGGER TD_SECTEUR
AFTER DELETE ON SECTEUR FOR EACH ROW
EXECUTE PROCEDURE 
     -- Supprimer les occurrences correspondantes de la table INTERESSE.

     delete from INTERESSE
     where
          INTERESSE.ID_SECTEUR = :OLD.ID_SECTEUR;

/

and here's the message error :

ERROR:  syntax error at or near ":"
LINE 9:           :OLD.ID_SOURCE_PARAMETRE <> :NEW.ID_SOURCE_PARAMET...
                  ^
********** Error **********

ERROR: syntax error at or near ":"
SQL state: 42601
Character: 304

i tried deleting until "Trigger d'insertion" and still got an error

ERROR:  syntax error at or near "select"
LINE 9:      select count(*) into numrows
             ^
********** Error **********

ERROR: syntax error at or near "select"
SQL state: 42601
Character: 369

1st time using triggers... please help

EDIT: As @Laurenz Albe and @pozs correctly pointed out, it is indeed oracle syntax. This is odd, as i specified during extraction PostgreSQL. So i tried with another Postgre9.1 and it generated this script:

--

------------------------------------------------------------------------------- 
--   Table : SOURCE_OPEN_DATA
-- ------------------------------------------------------------------------------- 

DROP TRIGGER TU_SOURCE_OPEN_DATA;

-- TRIGGER DE MODIFICATION --------------------------------

CREATE TRIGGER TU_SOURCE_OPEN_DATA
AFTER UPDATE ON SOURCE_OPEN_DATA
REFERENCING OLDROW, NEWROW 
FOR EACH ROW

IMPORT
import java.sql.* ;
BEGIN
     -- Interdire la modification de la clé étrangère référençant la table 
     -- SOURCE_PARAMETRE.

     if
          OLDROW.getValue(10, CHAR);.ID_SOURCE_PARAMETRE <> NEWROW.getValue(10, CHAR);.ID_SOURCE_PARAMETRE
     then
               raise_application_error(
               -20008,
               'Modification de la clé étrangère référençant "SOURCE_PARAMETRE" interdite.');
     end if;

END;

DROP TRIGGER TI_SOURCE_OPEN_DATA;

-- TRIGGER D'INSERTION --------------------------------------

CREATE TRIGGER TI_SOURCE_OPEN_DATA
AFTER INSERT ON SOURCE_OPEN_DATA 
REFERENCING OLDROW, NEWROW 
FOR EACH ROW

IMPORT
import java.sql.* ;
BEGIN

END;

Again, this is just an extract. Now I get

ERROR:  syntax error at or near ";"
LINE 11: DROP TRIGGER TU_SOURCE_OPEN_DATA;
                                         ^
********** Error **********

ERROR: syntax error at or near ";"

You cannot use Oracle syntax here. Create a trigger function (RETURNS trigger) and use that function in CREATE TRIGGER. — Laurenz Albe
– Laurenz Albe, Commented Feb 7, 2017 at 13:50
That's just not valid syntax for PostgreSQL. From the raise_application_error function calls, it seems that it could be generated for Oracle. If you can, generate these scripts for PostgreSQL specifically. — pozs
– pozs, Commented Feb 7, 2017 at 13:50
... interdire la création d'une occurrence de SOURCE_OPEN_DATA -- s'il n'existe pas d'occurrence correspondante dans la table SOURCE_PARAMETRE That is what foreign key constraints are intended for. — joop
– joop, Commented Feb 7, 2017 at 13:51
@joob SOURCE_OPEN_DATA has the same PK as SOURCE_PARAMETER. In french, I did what is refered to as "heritance". It's probably why that is included. — Elliott Addi
– Elliott Addi, Commented Feb 7, 2017 at 14:05
@ElliottAddi Precisely. Porting between databases is painful. You might want to check out ora2pg, maybe it can make the task easier. — Laurenz Albe
– Laurenz Albe, Commented Feb 7, 2017 at 14:11

Łukasz Kamiński · Accepted Answer · 2017-02-07 14:28:15Z

TL;DR; This is not proper PostgreSQL trigger syntax and has some errors that I think wouldn't work in any other database either.

You cannot in PostgreSQL execute SQL statement as procedure for trigger. You need to create actual procedure/function and supply it for trigger.

In addition to this, IF condition is missing some parts (ROW type it is referencing) and has semicolons for no reason. Trigger has no access to NEWROW, instead it is called NEW.

There is no REFERENCING keyword for PostgreSQL triggers, instead there is FROM keyword, but shouldn't be used in most cases.

SELECT INTO requires variable to be declared, so you need plpgsql language instead of plain SQL.

--example trigger procedure
CREATE OR REPLACE FUNCTION prevent_update() RETURNS TRIGGER AS $func$
BEGIN
    RAISE EXCEPTION 'You cannot modify whatever column';
    RETURN OLD; --not really needed
END;
$func$ LANGUAGE plpgsql;

--example trigger
CREATE TRIGGER TU_SOURCE_OPEN_DATA
AFTER UPDATE ON SOURCE_OPEN_DATA
FOR EACH ROW
WHEN (OLD.ID_SOURCE_PARAMETRE IS DISTINCT FROM NEW.ID_SOURCE_PARAMETRE)
EXECUTE PROCEDURE prevent_update();

In addition to this, you can use per column permission in PostgreSQL if you don't want certain users to modify data in certain fields. But if I remember correct it will throw error even if you do something like:

UPDATE table SET column1 = column1;

So you would have to omit said column completely.

Samuel Teixeira · Accepted Answer · 2017-02-07 13:51:33Z

0

You can try remove the : from :OLD and :NEW. You can see in the documentation (https://www.postgresql.org/docs/9.2/static/plpgsql-trigger.html), there is not : before the old and new.

answered Feb 7, 2017 at 13:51

Samuel Teixeira

3562 silver badges12 bronze badges

5 Comments

Elliott Addi Over a year ago

By changing the extraction destination from PostgreSQL to Postgre9.1, it also changed the script. See edit. It also changed the :OLD and :NEW

Samuel Teixeira Over a year ago

Did you try : DROP TRIGGER TU_SOURCE_OPEN_DATA on SOURCE_OPEN_DATA;

Elliott Addi Over a year ago

now i it tells me there is a syntax error at or near REFERENCING, yay

Samuel Teixeira Over a year ago

This tool is not generation a valid syntax for PostgreSQL. The REFERENCING is not in the "trigger" documentation.

Elliott Addi Over a year ago

You're right, just saw it in the doc. I'll try some other solutions in mind:

Collectives™ on Stack Overflow

Error writing trigger script on PostgreSQL

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related