I am trying to write a trigger function in postgress 9.5. But i get an error when trying to execute the script.
My trigger:
CREATE TABLE batch (
id bigserial PRIMARY KEY,
batch_type varchar(60) NOT NULL,
external_id varchar(255),
status varchar(20) NOT NULL,
CONSTRAINT unique_external_id UNIQUE(batch_type,external_id)
);
CREATE TABLE batch_wip (
batch_id BIGINT NOT NULL,
PRIMARY KEY (batch_id)
);
CREATE OR REPLACE FUNCTION insert_records_into_batch_wip()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.status = 'STARTED' THEN
INSERT INTO batch_wip(batch_id) VALUES(NEW.id);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER new_batch_record_inserts
AFTER INSERT
ON batch
FOR EACH ROW
EXECUTE PROCEDURE insert_records_into_batch_wip();
Error Iam getting,
Error occurred while executing flywayMigrate
Migration V14__add_work_in_progress_table_and_trigger.sql failed
SQL State : 42001 Error Code : 42001 Message : Syntax error in SQL statement "CREATE OR REPLACE FUNCTION[*] INSERT_RECORDS_INTO_BATCH_WIP() RETURNS TRIGGER AS $BODY$ BEGIN
IF NEW.STATUS = 'STARTED' THEN
INSERT INTO BATCH_WIP(BATCH_ID) VALUES(NEW.ID) "; expected "FORCE, VIEW, ALIAS, SEQUENCE, USER, TRIGGER, ROLE, SCHEMA, CONSTANT, DOMAIN, TYPE, DATATYPE, AGGREGATE, LINKED, MEMORY, CACHED, LOCAL, GLOBAL, TEMP, TEMPORARY, TABLE, PRIMARY, UNIQUE, HASH, SPATIAL, INDEX"; SQL statement:
CREATE OR REPLACE FUNCTION insert_records_into_batch_wip() RETURNS trigger AS $BODY$ BEGIN IF NEW.status = 'STARTED' THEN INSERT INTO batch_wip(batch_id) VALUES(NEW.id) [42001-191] Location : /Users/bla/migration/V14__add_work_in_progress_table_and_trigger.sql (/Users/bla/migration/V14__add_work_in_progress_table_and_trigger.sql) Line : 11 Statement : CREATE OR REPLACE FUNCTION insert_records_into_batch_wip() RETURNS trigger AS $BODY$ BEGIN
IF NEW.status = 'STARTED' THEN
INSERT INTO batch_wip(batch_id) VALUES(NEW.id)
Can someone figure out what wrong with my sql script?
CREATE TRIGGER
, it saysON batch
, but there is no such table. Did you paste the example here correctly? – redneb Sep 6 at 22:42INSERT
line is being treated as the end of the statement. I guess the tool you're using to run it is not recognising the$BODY$
delimiters. – Nick Barnes Sep 7 at 1:07