Why we Need Trigger:
1- Triggers help the
database designer ensure specific actions, such as maintaining an audit file,
are completed regardless of which program or user makes changes to the data.
2- To improve data
integrity, the trigger can be used. When an action is performed on data, it is
possible to check if the manipulation of the data concurs with the underlying
business rules, and thus avoids erroneous entries in a table.
There are three types of triggers available in PostgreSQL
1- After Trigger
2- Before Trigger
3- instead of Trigger
When
|
Event
|
Row-level
|
Statement-level
|
BEFORE
|
INSERT/UPDATE/DELETE
|
Tables
|
Tables and views
|
AFTER
|
INSERT/UPDATE/DELETE
|
Tables
|
Tables and views
|
INSTEAD OF
|
INSERT/UPDATE/DELETE
|
Views
|
—
|
TRUNCATE
|
—
|
Tables
|
For example creating table first:
CREATE TABLE public.employee
(
empid serial NOT NULL,
fname text,
lname text,
username text,
password text,
contact text,
salary numeric NOT NULL DEFAULT 0.0,
doj date NOT NULL
);
|
After Trigger:
This trigger occurs after the operation has completed (after constraints are checked and the INSERT, UPDATE,
or DELETE has completed).
Syntax:
CREATE
TRIGGER trigger_name
AFTER <action/
insert/delete/update> ON Table_Name
FOR EACH ROW
EXCECUTE PROCEDURE function_name
|
Callback
function: This
function will execute automatically through trigger "employee_update"
once an event occurs on the "Employee."
CREATE OR REPLACE FUNCTION check_employee_update() RETURNS TRIGGER AS $employee$
BEGIN
INSERT INTO audit
(
empid,
fname,
lname,
username,
password,
contact,
salary,
doj,
created_date
)
VALUES
(
new.empid,
new.fname,
new.lname,
new.username,
new.password,
new.contact,
new.salary,
new.doj,
current_timestamp
);
RETURN NEW;
END;
$employee$ LANGUAGE plpgsql;
|
Example for UPDATE (AFTER TRIGGER).
Any changes in a row will trigger the
following example.
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_update();
|
1- Insert records in employee table-
No Action
2- Update records in employee table-
Trigger fire
3- Delete records from employee
table- No action
If you want to execute
check_employee_update function if salary value will be changed, see the
following trigger example.
CREATE TRIGGER after_employee_update_for_salary
AFTER UPDATE Of Salary ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_update();
|
1- Insert records in employee table-
No Action
2- Update records in employee table- The trigger will fire but only when salary
will update.
3- Delete records from employee
table- No action
Example for INSERT (AFTER TRIGGER):
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_update();
|
1- Insert records in employee table-
Trigger fire
2- Update records in employee table-
No Action
3- Delete records from employee
table- No Action
Example for DELETE (AFTER TRIGGER):
If you are using the trigger on delete
you have to make a little bit change in the above function (check_employee_update)
because we are considering new value in check_employee_update while it will be
not finding any new records, it will work for the INSERT and UPDATE but failed
for DELETE, I am creating a new function as below for the Delete trigger.
CREATE OR REPLACE FUNCTION check_employee_delete() RETURNS TRIGGER AS $employee$
BEGIN
INSERT INTO audit
(
empid,
fname,
lname,
username,
password,
contact,
salary,
doj,
created_date
)
VALUES
(
old.empid,
old.fname,
old.lname,
old.username,
old.password,
old.contact,
old.salary,
old.doj,
current_timestamp
);
RETURN OLD;
END;
$employee$ LANGUAGE plpgsql;
|
Note: I am considering old value after
delete. If you are using the same audit table for all operations, then you can use a
flag column in the audit table to recognize the action.
Trigger:
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_delete();
|
1- Insert records in employee table-
No Action
2- Update records in employee table-
No Action
3- Delete records from employee
table- Trigger fire
Before Trigger
This trigger occurs before the
operation has completed (after constraints are checked and the INSERT, UPDATE,
or DELETE has ended).
Syntax:
CREATE
TRIGGER trigger_name
BEFORE <action/
insert/delete/update> ON Table_Name
FOR EACH ROW
EXCECUTE PROCEDURE function_name
|
Note: BEFORE TRIGGER is used when you
want to perform any task before any changes affect the table.
Example for UPDATE (BEFORE TRIGGER)
I am using the same function above
described
CREATE OR REPLACE FUNCTION check_employee_update() RETURNS TRIGGER AS $employee$
BEGIN
INSERT INTO audit
(
empid,
fname,
lname,
username,
password,
contact,
salary,
doj,
created_date
)
VALUES
(
new.empid,
new.fname,
new.lname,
new.username,
new.password,
new.contact,
new.salary,
new.doj,
current_timestamp
);
RETURN NEW;
END;
$employee$ LANGUAGE plpgsql;
|
Before Trigger:
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_update();
|
1- Insert records in employee table-
No Action
2- Update records in employee table-
Trigger fire
3- Delete records from employee
table- No action
If you want to execute
check_employee_update function if salary value will be changed, see the
following trigger example.
CREATE TRIGGER before_employee_update_for_salary
BEFORE UPDATE Of Salary ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_update();
|
1- Insert records in employee table-
No Action
2- Update records in employee table- The trigger will fire but only when salary
will update.
3- Delete records from employee
table- No action
Example for INSERT (BEFORE TRIGGER):
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_update();
|
1- Insert records in employee table- Trigger fire
2- Update records in employee table-
No Action
3- Delete records from employee
table- No Action
Example for DELETE (BEFORE TRIGGER)
For the BEFORE DELETE TRIGGER for
the employee table I will follow the same as above (After Delete in AFTER
TRIGGER) like create a new function for delete that will insert the old records
in audit.
CREATE TRIGGER before_employee_delete
BEFORE DELETE ON employee
FOR EACH ROW
EXECUTE PROCEDURE check_employee_delete(); |
1- Insert records in employee table- No Action
2- Update records in employee table-
No Action
3- Delete records from employee
table- Trigger fire
INSTEAD of Trigger
INSTEAD of Trigger operation (in the
case of inserts, updates, or deletes on a view).
Suppose we have a view in PostgreSQL
and you want to perform a trigger event then you have to choose the INSTEAD of
TRIGGER. I am creating a VIEW, please refer to the following PostgreSQL statement
to create VIEW.
CREATE VIEW employee_view AS
SELECT * FROM employee
|
I have only one table (employee) so
create a VIEW using it but the purpose of VIEW and its implementation I will
describe in the next article.
If I am going to use INSTEAD of
TRIGGER, then I have to make some changes in function.
CREATE OR REPLACE FUNCTION check_employee_view_update() RETURNS TRIGGER AS $employee$
BEGIN
INSERT INTO employee
(
empid,
fname,
lname,
username,
CREATE OR REPLACE FUNCTION check_employee_view_update() RETURNS TRIGGER AS $employee$
BEGIN
INSERT INTO employee
(
empid,
fname,
lname,
username,
password,
contact,
salary,
doj
)
SELECT
NEW.empid,
new.fname,
new.lname,
new.username,
new.password,
new.contact,
new.salary,
new.doj;
INSERT
INTO audit
(
empid,
fname,
lname,
username,
password,
contact,
salary,
doj,
created_date
)
VALUES
(
NEW.empid,
new.fname,
new.lname,
new.username,
new.password,
new.contact,
new.salary,
new.doj,
current_timestamp
);
RETURN
NEW;
END;
$employee$ LANGUAGE plpgsql;
|
In the above function, you can see I am
inserting data into table employee and table audit because we know VIEW is a
statement and its fetch the value from the tables at run time when we INSERT
the row into VIEW we have to write some query as above function to insert rows
at its related table.
INSERT
SOME Records into VIEW as below,
INSERT INTO employee_view
(
empid,
fname,
lname,
username,
password,
contact,
salary,
doj
)
VALUES
(
101,
'Naina',
'Singh',
'naina@gmail.com',
'xyz',
'9088877767',
120000,
current_timestamp
);
|
You will see the inserted records as you wish.
Same as it is if you want to perform
trigger on another action like UPDATE/DELETE, you have to change the function
according to your requirements.
Please comment your
suggestion/question/understanding in comment section.
You have any query you can directly
contact me on dilip.ravi084@gmail.com, At the weekend definitely, I will reply.
No comments:
Post a Comment
Please do not enter any spam link in the comment box.