Error creating Trigger in Postgresql - syntax error at or near "BEGIN"

Question

Error creating Trigger in Postgresql - syntax error at or near "BEGIN"

Navigation

#1 by (2 votes)
#2 by (0 votes)

0

I am currently trying to create a trigger in postgresql to perform an insert in a table. But when executing the sentence the result throws:

ERROR: syntax error at or near "BEGIN" LINE 4: BEGIN

CREATE TRIGGER TRG_INS_DOCUMETTYPE
AFTER INSERT ON DOCUMENTTYPE
FOR EACH ROW
BEGIN   
INSERT INTO AUD_DOCUMENTTYPE (
DOCT_ID,
DOCT_NAME,
DOCT_REGISTERBY,
DOCT_REGISTERDATE,
DOCT_OPERATION
) VALUES (    
:NEW.DOCT_ID,
:NEW.DOCT_NAME,
:NEW.DOCT_REGISTERBY,
:NEW.DOCT_REGISTERDATE,
'INSERT'
);
END;

What have I got wrong?

postgresql trigger postgresql-9.3

asked by Andres Guillermo Castellanos A 21.11.2016 в 19:46

source

2 answers

0

The first thing you have to do is create a function that will be executed in the trigger. Something like this:

Create or replace function nombre_funcion() 
  RETURNS trigger 
  AS 
  $$
begin
INSERT INTO AUD_DOCUMENTTYPE (
DOCT_ID,
DOCT_NAME,
DOCT_REGISTERBY,
DOCT_REGISTERDATE,
DOCT_OPERATION
) VALUES (    
DOCT_ID,
DOCT_NAME,
DOCT_REGISTERBY,
DOCT_REGISTERDATE,
'INSERT'
);

RETURN NEW;
END;
$$ 
LANGUAGE plpgsql;

Then you create the trigger:

CREATE TRIGGER TRG_INS_DOCUMETTYPE
AFTER INSERT ON DOCUMENTTYPE
FOR EACH ROW
EXECUTE PROCEDURE nombre_funcion();

answered by 21.11.2016 в 20:03

I have a Class A with instance of class B and class B has an instance of class A Objective-C How to save the pointer to a static method in a variable?

score 2 · Accepted Answer

The syntax you have is for Oracle. Although I am not an expert with triggers in PostgreSQL, it seems that you must define them differently. You need to do it in 2 stages:

Create a function that contains logic.

Create the trigger that calls the function.

Here is an example that should work correctly in your case.

Function:

create function TRG_INS_DOCUMETTYPE_FUNCTION()
returns trigger as
$BODY$
BEGIN   
INSERT INTO AUD_DOCUMENTTYPE (
DOCT_ID,
DOCT_NAME,
DOCT_REGISTERBY,
DOCT_REGISTERDATE,
DOCT_OPERATION
) VALUES (    
NEW.DOCT_ID,
NEW.DOCT_NAME,
NEW.DOCT_REGISTERBY,
NEW.DOCT_REGISTERDATE,
'INSERT'
);

return new;

END;
$BODY$ language plpgsql;

Trigger:

CREATE TRIGGER TRG_INS_DOCUMETTYPE
AFTER INSERT ON DOCUMENTTYPE
FOR EACH ROW
execute procedure TRG_INS_DOCUMETTYPE_FUNCTION();

Demo