SUMMARY: This article discussed triggers in PostgreSQL. It describes the different types of triggers and provides examples for each:
1. Types of Triggers
2. Creating a Trigger
1. INSERT event trigger
2. UPDATE event trigger
3. DELETE event trigger
4. Dropping a Trigger
5. Uses of Triggers
6. Important Points to Remember
A “trigger” is defined as any event that sets a course of action in a motion.
In PostgreSQL, if you want to take action on specific database events, such as INSERT, UPDATE, DELETE, or TRUNCATE, then trigger functionality can be useful as it will invoke the required function on defined events.
The trigger will be associated with the specified table, view, or foreign table and will execute the specified function when certain operations are performed on that table. Depending on the requirement we can create trigger BEFORE, AFTER or INSTEAD of the events/operation.
Types of Triggers
1. Row Level Trigger: If the trigger is marked FOR EACH ROW then the trigger function will be called for each row that is getting modified by the event.
For example: If we UPDATE 100 rows in the table, the UPDATE trigger function will be called 100 times, once for each updated row.
2. Statement Level Trigger: The FOR EACH STATEMENT option will call the trigger function only once for each statement, regardless of the number of the rows getting modified.
Creating a Trigger
The SQL command CREATE TRIGGER creates a trigger on the specified object.
The syntax of the CREATE TRIGGER is as follows:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE { FUNCTION | PROCEDURE } function_name ( arguments )
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
For a more detailed description and available options, check the PostgreSQL documentation at https://www.postgresql.org/docs/12/sql-createtrigger.html.
1. INSERT event Trigger
The INSERT event trigger gets called when a new record is added to a table using the INSERT statement.
Example
Let’s take a look at an example of creating a new trigger. In this example, we will create a new table named “Employee” as follows:
CREATE TABLE "Employee"
(
"EmployeeId" INT NOT NULL,
"LastName" VARCHAR(20) NOT NULL,
"FirstName" VARCHAR(20) NOT NULL,
"Title" VARCHAR(30),
"ReportsTo" INT,
"BirthDate" TIMESTAMP,
"HireDate" TIMESTAMP,
"Address" VARCHAR(70),
"City" VARCHAR(40),
"State" VARCHAR(40),
"Country" VARCHAR(40),
"PostalCode" VARCHAR(10),
"Phone" VARCHAR(24),
"Fax" VARCHAR(24),
"Email" VARCHAR(60),
CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId")
);
CREATE TABLE "Employee_Audit"
(
"EmployeeId" INT NOT NULL,
"LastName" VARCHAR(20) NOT NULL,
"FirstName" VARCHAR(20) NOT NULL,
"UserName" VARCHAR(20) NOT NULL,
"EmpAdditionTime" VARCHAR(20) NOT NULL,
);
We are going to create a trigger that can add an entry in the “Employee_Audit” table if a new employee record gets inserted into the “Employee” table. We are going to log the username details, which is going to add the record in the “Employee” table.
Add a trigger function and CREATE TRIGGER command:
CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc()
RETURNS trigger AS
$$
BEGIN
INSERT INTO "Employee_Audit" ( "EmployeeId", "LastName", "FirstName","UserName" ,"EmpAdditionTime")
VALUES(NEW."EmployeeId",NEW."LastName",NEW."FirstName",current_user,current_date);
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
CREATE TRIGGER employee_insert_trigger
AFTER INSERT
ON "Employee"
FOR EACH ROW
EXECUTE PROCEDURE employee_insert_trigger_fnc();
Once we create the above INSERT trigger on the table, it will add one new entry to the “Employee_Audit” table with these details:
trigger_demo=# INSERT INTO "Employee" VALUES(10,' Adams','Andrew','Manager',1,'1962-02-18 00:00:00','2010-08-14 00:00:00','11120 Jasper Ave NW','Edmonton','AB','Canada','T5K 2N1','+1 780 428-9482','+1 780 428-3457','abc@gmail.com');
INSERT 0 1
trigger_demo=# select * from "Employee" where "EmployeeId" =10;
-[ RECORD 1 ]-------------------
EmployeeId | 10
LastName | Adams
FirstName | Andrew
Title | Manager
ReportsTo | 1
BirthDate | 1962-02-18 00:00:00
HireDate | 2010-08-14 00:00:00
Address | 11120 Jasper Ave NW
City | Edmonton
State | AB
Country | Canada
PostalCode | T5K 2N1
Phone | +1 780 428-9482
Fax | +1 780 428-3457
Email | abc@gmail.com
trigger_demo=#
trigger_demo=# select * from "Employee_Audit" ;
-[ RECORD 1 ]---+-----------
EmployeeId | 10
LastName | Adams
FirstName | Andrew
UserName | postgres
EmpAdditionTime | 2019-11-10
2. UPDATE event Trigger
The UPDATE event trigger gets called at the time of UPDATE statement execution.
Example
CREATE TRIGGER verify_user_for_update
BEFORE UPDATE
ON "Employee"
FOR EACH ROW
EXECUTE PROCEDURE employee_verify_user_priv();
3. DELETE event Trigger
This the DELETE event trigger, which can be added on transactions that DELETE the records.
Example
CREATE TRIGGER employee_delete_trigger
AFTER DELETE
ON "Employee"
FOR EACH ROW
EXECUTE PROCEDURE aft_delete();
Dropping a Trigger
DROP TRIGGER is used to remove a trigger. The syntax is very simple.
drop trigger employee_insert_trigger on "Employee" ;
Uses Of Triggers
1. Auditing: You can use triggers to track the table transactions by logging the event details.
2. Forcing Check Constraint: You can create a trigger by which you can check the constraints before applying the transaction to the table.
3. Automatic Population: By using triggers you can also auto populate tables fields by new transactions records manipulation.
Important Points To Remember
1. To create a trigger on a table, the user must have the TRIGGER privilege on the table and EXECUTE privilege on the trigger function.
2. You can check system catalogue “pg_trigger” for the existing trigger information in the database.
3. If you create multiple triggers on the same object for the same event, those triggers will be fired in alphabetical order by name.
Reference Links
https://www.postgresql.org/docs/12/sql-createtrigger.html
http://www.postgresqltutorial.com/introduction-postgresql-trigger/
Popular Links
Featured Links
Relevant Blogs
EDB Tutorial: How to run a TPC-E-like benchmark easily
The TPC Benchmark(TM) E (TPC-E) was released back in 2007 as the successor to the TPC Benchmark(TM) C (TPC-C) for an OLTP type workload. To briefly recap, the TPC-E models...EDB Tutorial: How to Use Privilege Analysis to Identify [Un]Used Privileges
Have you ever searched for unnecessary granted privileges in your database? To find these, you need to know 1. The granted privileges 2. How many of the granted privileges have...EDB Tutorial: How To Get the Most Out of EDB Query Advisor
Enterprise-class databases require DBAs who are responsible for maximizing query performance. Therefore, when it comes to large scale deployment of databases, minimizing the work of DBA is important. One of...More Blogs
EDB Tutorial: How to Configure Databases for EDB JDBC SSL Factory Classes
The fact is, SSL configuration required by EDB JDBC SSL factory classes is considered confusing and challenging by many EDB customers. This stems from the concepts behind the SSL handshake...