How To Create Triggers In PostgreSQL || How To Create Trigger Function I...

In this comprehensive tutorial, we delve into the world of triggers in PostgreSQL, showing you how to create both triggers and trigger functions from scratch. Triggers are a powerful feature in PostgreSQL that allow you to automatically execute a function when certain events occur on a table, such as INSERT, UPDATE, or DELETE operations.

We start by explaining the concept of triggers and their importance in maintaining data integrity and automating routine tasks. You’ll learn how to define a trigger function, which contains the logic that will be executed when the trigger is fired. From there, we guide you through the process of creating a trigger that links this function to a specific table, ensuring that your database reacts automatically to changes.

The video includes detailed, step-by-step examples, making it easy for you to follow along and implement these techniques in your own PostgreSQL environment. We also cover best practices for managing and optimizing triggers, ensuring that they perform efficiently without negatively impacting your database.

Whether you're a database administrator, developer, or just starting with PostgreSQL, this tutorial will equip you with the knowledge needed to effectively use triggers to automate and secure your database operations.

PostgreSQL triggers, create triggers PostgreSQL, trigger functions PostgreSQL, SQL triggers, database automation, PostgreSQL tutorial, database management, trigger functions examples, PostgreSQL triggers step-by-step, SQL automation

-- Table: public.students

-- DROP TABLE IF EXISTS public.students;

CREATE TABLE IF NOT EXISTS public.students

(

    roll numeric(10,0),

    name character varying(30) COLLATE pg_catalog."default",

    course character varying(30) COLLATE pg_catalog."default"

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.students

    OWNER to postgres;

-- Table: public.students_logs

-- DROP TABLE IF EXISTS public.students_logs;

CREATE TABLE IF NOT EXISTS public.students_logs

(

    roll_old numeric(10,0),

    name_old character varying(30) COLLATE pg_catalog."default",

    course_old character varying(30) COLLATE pg_catalog."default"

)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.students_logs

    OWNER to postgres;

-- FUNCTION: public.student_logs_trg_func()

-- DROP FUNCTION IF EXISTS public.student_logs_trg_func();

CREATE OR REPLACE FUNCTION public.student_logs_trg_func()

    RETURNS trigger

    LANGUAGE 'plpgsql'

    COST 100

    VOLATILE NOT LEAKPROOF

AS $BODY$

declare

begin

insert into students_logs

(roll_old,name_old,course_old)

values(old.roll,old.name,old.course);

return new;

end;

$BODY$;

ALTER FUNCTION public.student_logs_trg_func()

    OWNER TO postgres;

-- Trigger: student_trg

-- DROP TRIGGER IF EXISTS student_trg ON public.students;

CREATE TRIGGER student_trg

    BEFORE DELETE OR UPDATE 

    ON public.students

    FOR EACH ROW

    EXECUTE FUNCTION public.student_logs_trg_func();

insert into students values (1,'Akram','MCA');

select * from students;

select * from students_logs;

update students set name = 'Akram Sohail' where roll = 1;

update students set name = 'Akram Sohail (Modified)' where roll = 1;

delete from students where roll = 1;

----------------------------------------------------------------------------------------------

Triggers are special types of functions that are called/invoked/executed/performed automatically as per the trigger event declaration. A trigger is usually set before or after updating, deleting, insert DML statements on a table. That means, whenever a record is to be inserted, updated, or deleted, based on the time of execution which can be before or after, the trigger executes. Generally, the execution of the Trigger is called “Trigger Fired”.

In PostgreSQL, to create a trigger and make it usable, the sequence of object creation will be:

•   Create a Table on which a trigger to be implemented

•   Create another table to keep logs, so we can see the Trigger work

•   Create a Trigger Function that holds the trigger

•   Create the Trigger Function and Define the Trigger Logic

•   Perform Insert/Update/Delete Operations on the Table