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 😢.
-- 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 PostgreSQL, an audit trigger is a mechanism that allows you to monitor and record changes to database tables. It helps in maintaining data integrity, tracking modifications, and ensuring compliance with regulatory requirements. When certain events or actions occur, such as INSERT, UPDATE, or DELETE operations on specific tables, the audit trigger is triggered, and it performs predefined actions to capture relevant information.
Here's how audit triggers work in PostgreSQL:
Defining Audit Triggers: To implement audit triggers, you need to define them on the tables you want to monitor. An audit trigger is a database object associated with a specific table and set of events. It consists of trigger functions and rules that define the desired behavior when the associated events occur.
Trigger Functions: A trigger function is a user-defined function that gets executed when the associated event is triggered. In the context of audit triggers, the trigger function typically captures the necessary information about the event and inserts it into an audit table or log.
Audit Tables or Logs: An audit table or log is a separate table or set of tables where the audit trail is stored. This is where the trigger function inserts the relevant information about the event, such as the user who performed the action, the timestamp, the old and new values (in case of UPDATE operations), and any other desired metadata.
Event Types: You can configure audit triggers to fire on specific events, such as INSERT, UPDATE, or DELETE operations. This allows you to customize the level of detail captured in the audit trail based on your requirements. For example, you might choose to audit only certain tables or specific columns within those tables.
Trigger Rules: Trigger rules define the conditions under which the audit trigger should be fired. For example, you can specify that the trigger should only be activated when a specific column is modified or when a certain condition is met.
Enabling and Disabling Audit Triggers: Once you have defined the audit triggers, you can enable or disable them as needed. This gives you flexibility in controlling when the triggers are active, such as during specific maintenance or auditing periods.
Analyzing Audit Data: The captured audit trail can be analyzed to gain insights into the database activity, identify potential issues or anomalies, and meet compliance requirements. By reviewing the audit logs, you can track changes, detect unauthorized actions, and investigate any suspicious activities.
It's important to note that implementing audit triggers requires careful consideration of performance and storage implications. Storing detailed audit logs can generate a significant amount of data, so it's essential to strike a balance between capturing sufficient information and managing resource usage effectively.
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.
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 😢.
insert into students values (1, 'Akram Sohail', 'MCA');
select * from students;
select * from students_logs;
update students set course = 'MCA 2018-2019';
update students set name = 'A. Sohail';
update students set roll = 2;
delete from students_logs;
delete from students;
-- 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
(
roll_old numeric(10,0),
name_old character varying(30) COLLATE pg_catalog."default",
course_old character varying(30) COLLATE pg_catalog."default",
actions character varying(50) 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
if old.roll <> new.roll then
insert into students_logs
(roll_old,name_old,course_old, actions)
values(old.roll,old.name,old.course, 'Roll Value Updated');
end if;
if old.name <> new.name then
insert into students_logs
(roll_old,name_old,course_old, actions)
values(old.roll,old.name,old.course, 'Name Value Updated');
end if;
if old.course <> new.course then
insert into students_logs
(roll_old,name_old,course_old, actions)
values(old.roll,old.name,old.course, 'Course Value Updated');
end if;
return new;
end;
$BODY$;
ALTER FUNCTION PUBLIC.STUDENT_LOGS_TRG_FUNC() OWNER TO POSTGRES;
In PostgreSQL, an audit trigger is a mechanism that allows you to monitor and record changes to database tables. It helps in maintaining data integrity, tracking modifications, and ensuring compliance with regulatory requirements. When certain events or actions occur, such as INSERT, UPDATE, or DELETE operations on specific tables, the audit trigger is triggered, and it performs predefined actions to capture relevant information.
Here's how audit triggers work in PostgreSQL:
Defining Audit Triggers: To implement audit triggers, you need to define them on the tables you want to monitor. An audit trigger is a database object associated with a specific table and set of events. It consists of trigger functions and rules that define the desired behavior when the associated events occur.
Trigger Functions: A trigger function is a user-defined function that gets executed when the associated event is triggered. In the context of audit triggers, the trigger function typically captures the necessary information about the event and inserts it into an audit table or log.
Audit Tables or Logs: An audit table or log is a separate table or set of tables where the audit trail is stored. This is where the trigger function inserts the relevant information about the event, such as the user who performed the action, the timestamp, the old and new values (in case of UPDATE operations), and any other desired metadata.
Event Types: You can configure audit triggers to fire on specific events, such as INSERT, UPDATE, or DELETE operations. This allows you to customize the level of detail captured in the audit trail based on your requirements. For example, you might choose to audit only certain tables or specific columns within those tables.
Trigger Rules: Trigger rules define the conditions under which the audit trigger should be fired. For example, you can specify that the trigger should only be activated when a specific column is modified or when a certain condition is met.
Enabling and Disabling Audit Triggers: Once you have defined the audit triggers, you can enable or disable them as needed. This gives you flexibility in controlling when the triggers are active, such as during specific maintenance or auditing periods.
Analyzing Audit Data: The captured audit trail can be analyzed to gain insights into the database activity, identify potential issues or anomalies, and meet compliance requirements. By reviewing the audit logs, you can track changes, detect unauthorized actions, and investigate any suspicious activities.
It's important to note that implementing audit triggers requires careful consideration of performance and storage implications. Storing detailed audit logs can generate a significant amount of data, so it's essential to strike a balance between capturing sufficient information and managing resource usage effectively.
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.
