Elliott Addi Elliott Addi - 1 year ago 105
SQL Question

Error writing trigger script on PostgreSQL

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 ";"

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download