In this continuation of our PostgreSQL trigger series, we focus on creating audit triggers using trigger functions. Audit triggers are an essential tool for tracking and logging changes in your database, ensuring that you maintain a robust record of all operations that modify your data.
This tutorial begins with a brief recap of trigger functions in PostgreSQL, highlighting how they can be used to automatically execute specific actions when certain events occur in your database. We then dive into the process of setting up audit triggers that log every INSERT, UPDATE, and DELETE operation performed on a table.
You'll learn how to define a trigger function that captures changes and writes them to an audit table, providing a detailed record of all modifications made to your data. The video includes step-by-step instructions, making it easy to follow along and implement these techniques in your own PostgreSQL environment.
Additionally, we discuss best practices for managing and optimizing your audit triggers, ensuring they run efficiently without negatively impacting your database's performance. By the end of this video, you'll have a thorough understanding of how to use PostgreSQL triggers for effective auditing and monitoring of your database activities.
PostgreSQL audit triggers, create audit triggers PostgreSQL, PostgreSQL trigger functions, audit logs PostgreSQL, database auditing PostgreSQL, PostgreSQL triggers tutorial, trigger functions SQL, database security PostgreSQL, PostgreSQL audit table, SQL trigger examples
-- SEQUENCE: public.students_logs_seq
-- DROP SEQUENCE IF EXISTS public.students_logs_seq;
CREATE SEQUENCE IF NOT EXISTS public.students_logs_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 999999999
CACHE 1;
ALTER SEQUENCE public.students_logs_seq
OWNER TO postgres;
-- 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;
-- Trigger: student_trg
-- DROP TRIGGER IF EXISTS student_trg ON public.students;
CREATE TRIGGER student_trg
AFTER INSERT OR DELETE OR UPDATE
ON public.students
FOR EACH ROW
EXECUTE FUNCTION public.student_logs_trg_func();
-- Table: public.students_logs
-- DROP TABLE IF EXISTS public.students_logs;
CREATE TABLE IF NOT EXISTS public.students_logs
(
logs_id numeric(10,0) NOT NULL DEFAULT nextval('students_logs_seq'::regclass),
roll_old numeric(10,0),
name_old character varying(30) COLLATE pg_catalog."default",
course_old character varying(30) COLLATE pg_catalog."default",
roll_new numeric(10,0),
name_new character varying(30) COLLATE pg_catalog."default",
course_new character varying(30) COLLATE pg_catalog."default",
actions character varying(50) COLLATE pg_catalog."default",
log_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT students_logs_pkey PRIMARY KEY (logs_id)
)
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
if old.roll <> new.roll then
insert into students_logs
(roll_old,name_old,course_old, roll_new,name_new,course_new,actions)
values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'Roll Value Updated');
end if;
if old.name <> new.name then
insert into students_logs
(roll_old,name_old,course_old, roll_new,name_new,course_new,actions)
values(old.roll,old.name,old.course,new.roll,new.name,new.course,'Name Value Updated');
end if;
if old.course <> new.course then
insert into students_logs
(roll_old,name_old,course_old, roll_new,name_new,course_new,actions)
values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'Course Value Updated');
end if;
-- For Insert
if old.roll is null then
insert into students_logs
(roll_old,name_old,course_old, roll_new,name_new,course_new,actions)
values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'New Record Inserted');
end if;
-- For Delete
if new.roll is null then
insert into students_logs
(roll_old,name_old,course_old, roll_new,name_new,course_new,actions)
values(old.roll,old.name,old.course,new.roll,new.name,new.course, 'Existing Record Deleted');
end if;
return new;
end;
$BODY$;
ALTER FUNCTION public.student_logs_trg_func()
OWNER TO postgres;
How To Create Audit Triggers In PostgreSQL || Trigger Functions In PostgreSQL || Part 2 Video
In this video, we will try to create an audit trigger function for PostgreSQL. This is also an extended video called Part 2 of the audit triggers series. Please watch the video completely to understand the concept of trigger functions in PostgreSQL.
In summary, audit triggers in PostgreSQL provide a powerful mechanism to monitor and record changes in database tables. By capturing relevant information about specific events, they enhance data integrity, assist in compliance efforts, and enable effective analysis of database activity.
