Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will talk about the general use of PostgreSQL Update Trigger with the help of examples.
First of all, we need to create a function or procedure before creating the trigger.
The purpose of doing this is that we will be performing the action to be performed in the create trigger command by using that function.
I think it will be better understood by making an example about the update trigger.
When the veliler table is updated, we will make a trigger that writes the id, adi, soyadi columns in which veliler table and which user made this update, when he did it and which action he did, to the veliler audit table.
First, we create a table named Veliler_audit that will keep the log of the updates.
1 2 3 4 5 6 7 8 9 | CREATE TABLE "Veliler_Audit" ( "Velilerid" INT NOT NULL, "İsim" VARCHAR(20) NOT NULL, "Soyisim" VARCHAR(20) NOT NULL, "UserName" VARCHAR(20) NOT NULL, "Tarih" VARCHAR(20) NOT NULL, "İşlem" VARCHAR(20) NOT NULL ); |
After creating our table, we need to create a function as follows to add our data to the parents_audit table.
Here, in the returns section, you can see that we used the Returns trigger command. The reason for this is because this function will be used in the trigger.
1 2 3 4 5 6 7 8 9 10 | CREATE OR REPLACE FUNCTION veliler_update_trigger_fnc() RETURNS trigger AS $$ BEGIN INSERT INTO "Veliler_Audit" ( "Velilerid", "İsim", "Soyisim","UserName" ,"Tarih","İşlem") VALUES(NEW."no",NEW."adi",NEW."soyadi",current_user,now(),'update'); RETURN NEW; END; $$ LANGUAGE 'plpgsql'; |
After creating our function, we create the trigger that we specified at the bottom of the function we created as follows.
1 2 3 4 5 | CREATE TRIGGER verify_user_for_update BEFORE UPDATE ON "veliler" FOR EACH ROW EXECUTE PROCEDURE veliler_update_trigger_fnc(); |
Let’s change record number 15 in the Parents table to Fatma.
1 | update veliler set adi ='Fatma' where no =15 |
As seen above, we see that our record has arrived here.
