Laravel migration error next to BEGIN when starting Postgress
I created a database trigger in mysql
and in laravel it works fine, however when I go to postgresql
I get the error:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "BEGIN"
(I am new to postgresql btw)
Here is the code I made during the migration:
DB::unprepared('CREATE TRIGGER histories_insert AFTER INSERT ON packets FOR EACH ROW
BEGIN
IF new.status = "pending" THEN
insert into `histories` (`packet_id`, `message`, `created_at`, `updated_at`) VALUES (new.id, "Barang berhasil di buat", now(), now());
END IF;
END');
I was really confused. Hope someone can help me :)
source to share
The syntax and structure in mysql and postgre are different, in Postgres we must first write a trigger function (procedure), then we called it in the body of our trigger
so after
FORE EACH ROW
Should be
when event __type_of_event__
and then we call our trigger function
execute procedure __procedure_name__
Take a look at the doc about trigger here
and the startup procedure is here
Noted that when event
this is optional and the name of your field inside the procedure should be changed to plain text or wrap it in double quotes (if mixed with an uppercase letter)
Don't worry about the docs version, the postgresql trigger is version compatible, hope it helps!
source to share
You need to follow the procedure:
From the docs:
PostgreSQL only allows you to execute a custom function for the triggered action. The standard allows a number of other SQL commands, such as CREATE TABLE, to be executed as an triggered action. It's easy to get around this limitation by creating a custom function that executes the commands you want.
DB::unprepared('CREATE TRIGGER histories_insert
AFTER INSERT ON packets
FOR EACH ROW
EXECUTE PROCEDURE that_procudure_you_wrote_with_that_content(new)');
source to share