Only 3.9% of viewers are subscribing to my channel 😓.
I request you to please give click on Subscribe button.
It really helps me grow 😢.
-- 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
