In this tutorial, you will learn how to use the PostgreSQL DELETE TRIGGER statement to delete a trigger from a table.
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.
Let’s make an example of the delete trigger.
When the deletion operation of the parents table is performed, we will create a trigger that writes the “id, adi, soyadi” columns in which parents table and which user did this delete operation, when he did it and what action was done in the parents audit table.
First, we create a table named Veliler_audit that will keep the log of deleted items.
|
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 Veliler_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_delete_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(),'delete'); 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 velilerdeletetrigger BEFORE delete ON "veliler" FOR EACH ROW EXECUTE PROCEDURE veliler_delete_trigger_fnc(); |
Let’s delete record number 14 in the Veliler table.
|
1 |
delete from veliler where "no" = 14 |
Let’s check if our record has been deleted.
