Some Benefits of Using Triggers
- Data validation can be done easily.
- The unique value of a new record can be generated.
- Application development becomes faster.
- The business rules of the application can be enforced.
- Maintenance of the application becomes easier.
Syntax
The syntax of creating a trigger is given below.
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 )
- CREATE keyword is used to create a new trigger, and REPLACE keyword is used to modify an existing trigger.
- The CONSTRAINT option is used to create a constraint trigger that is fired based on the constraint value.
- A unique trigger name is used for creating the trigger.
- Four types of events can be used to create a trigger. These are INSERT, UPDATE, DELETE, and TRUNCATE.
- Three types of triggers that can be created are BEFORE, AFTER, and INSTEAD OF. The triggers can be created for the table, foreign table, and view. BEFORE and AFTER triggers support INSERT, UPDATE, and DELETE operations for table and foreign table in row-level, and the view also in statement-level. INSTEAD OF trigger supports INSERT, UPDATE, and DELETE operations for view only in row-level. TRUNCATE event can be used for table only at statement-level.
- After defining the event, the name of the table or foreign table or view is used for which trigger will be created.
- The referenced_table_name is used for foreign-key constraint and the constraint trigger only.
- The default timing of the trigger is defined by NOT DEFERRABLE or DEFERRABLE or INITIALLY IMMEDIATE or INITIALLY DEFERRED.
- REFERENCING keyword is used to declare one or two relation names.
- OLD TABLE or NEW TABLE clause defines the before-image transition relation or the after-image transition relation.
- transition_relation_name is used within the trigger for the transition relation.
- FOR EACH ROW or FOR EACH STATEMENT defines whether the trigger function should be fired once for every row affected by the trigger event or just once per SQL statement. FOR EACH STATEMENT is the default.
- condition determines whether the trigger function will be executed or not.
- function_name is a user-defined function that takes no argument, and the return type is the trigger that is executed when the trigger fires.
- The comma-separated list of arguments is passed to the function when the trigger is executed.
Pre-requisites
A. Install the PostgreSQL
You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:
$ sudo systemctl start postgresql.service
B. Run the following command to log in to PostgreSQL with root privilege.
C. You can create a database named “testdb” by running the following SQL statement.
If you want to create a table into the testdb database, then you have to type “\c” to change the database. But in this tutorial, I didn’t change the database, and the table and all functions were created in the default database named postgres.
Create a table named teachers and courses with some records to test the use of triggers that will be created later in this tutorial.
id serial primary key,
name varchar(30),
address varchar(100),
contact_no varchar(20),
available bool default True);
CREATE TABLE courses (
course_id serial primary key,
name varchar(30),
credit_hour float,
teacher_id int default 0);
INSERT INTO teachers (name, address, contact_no, available )
values
('Nasir Uddin', 'Dhanmondi 15, Dhaka', '+8801947854523', False),
('Farzana Mahmud', '39/1, Jigatola, Dhaka', '+88018553426754', False);
INSERT INTO courses (name, credit_hour, teacher_id)
values
('Physics', 2.0, 1),
('Chemistry', 2.0, 2),
('Math', 3.0, 1);
Trigger Examples
Each trigger is associated with a PostgreSQL function. So, you have to create the function before creating the trigger. The way of creating different types of triggers has been shown in this part of the tutorial.
Example-1: Create BEFORE INSERT Trigger
The BEFORE INSERT triggers fires before performing any INSERT operation. Run the following SQL statement to create a function named before_insert() that will be used in a BEFORE INSERT trigger. One of the major purposes of using a trigger is data validation. The following function will check whether the particular fields are empty or not. If any of the field values remain empty, then a particular error message will be raised. Another validation will be checked for the name field, and an error message will be raised if the length of this field is less than 5. The next conditional statement is used to concatenate the string value “+88” with the contact_no if the length of the contact_no field is 11.
RETURNS trigger AS
$$
BEGIN
IF NEW.name = '' or NEW.address = '' or NEW.contact_no = '' THEN
raise 'Name or Address or contact_no field is empty.';
END IF;
IF length(NEW.name) < 5 THEN
raise 'Name field must contain more than 5 characters.';
END IF;
IF length(NEW.contact_no) = 11 THEN
NEW.contact_no = '+88' || NEW.contact_no;
END IF;
RETURN NEW;
END;
$$
LANGUAGE 'plpgsql';
The following output will appear if the function is created successfully in the postgres database.
Run the following SQL statement to create a trigger named check_before_insert that will execute the before_insert() function before inserting the new record to the teachers table.
BEFORE INSERT
ON teachers
FOR EACH ROW
EXECUTE FUNCTION before_insert();
The following output will appear if the trigger is created successfully.
Run the following INSERT statement that contains all valid data.
VALUES ('Zafar Iqbal', 'Mirpur, Dhaka', '01738975634');
The following output will appear if the new record is inserted successfully.
Run the following INSERT statement that contains the invalid data for the name field.
VALUES ('Lina', 'Malibag, Dhaka', '01790975630');
The following error message will appear for the invalid name value.
Run the following INSERT statement that contains the empty value for the address and contact_no fields, and the empty value for these fields is invalid.
VALUES ('Nilufar Rahman', '', '');
The following error message will appear for the empty values.
Run the following SELECT statement to check which records are inserted after using the BEFORE INSERT trigger.
The following output shows that only one record is inserted after using the trigger.
Example-2: Create AFTER DELETE Trigger
The AFTER DELETE trigger fires after deleting any record from the particular table. Run the following SQL statement to create a function named after_delete() that will be used in an AFTER DELETE trigger to delete the related rows from the courses table when a record is deleted from the teachers’ table. Here, an old.id has been used to trace the deleted id from the teachers’ table.
RETURNS TRIGGER AS
$$
BEGIN
DELETE FROM courses WHERE teacher_id = old.id;
RETURN old;
END;
$$
LANGUAGE plpgsql;
The following output will appear if the function is created successfully in the postgres database.
Run the following SQL statement to create a trigger named delete_teacher that will execute the after_delete() function after deleting a record from the teachers’ table that will delete the records from the courses table where the teacher_id value matches with the deleted id value of the teachers’ table.
AFTER DELETE
ON teachers
FOR EACH ROW
EXECUTE FUNCTION after_delete();
The following output will appear if the trigger is created successfully.
Run the following DELETE statement that will delete the record from the teachers’ table where the id value is 1. The delete_teacher trigger will be fired if any record is deleted from the teachers’ table.
The following output shows that one record has been deleted from the teachers’ table. So, the delete_teacher trigger will be fired and delete all records from the courses table that contains 1 in the teacher_id field.
Now, run the following SELECT commands to check which records are deleted from the teachers and courses tables.
SELECT * FROM courses;
The output shows that one record has been deleted from the teachers’ table, and two records have been deleted from the courses table by using the trigger.
Example-3: Create INSTEAD OF UPDATE Trigger
The INSTEAD OF UPDATE trigger can be applied on the table views only. So, you have to create a view of the table and a function to test the use of this type of trigger. Run the following SQL statement to create a function named insert_course() that will update the values of the teachers and the courses table after inserting a new record to the courses table. The purpose of this function is to assign a teacher for the new course if the teacher is available.
RETURNS trigger AS
$$
DECLARE
tec_id INT;
BEGIN
INSERT into courses(name, credit_hour)
VALUES(NEW.name, NEW.credit_hour);
SELECT id into tec_id from teachers where available = True limit 1;
UPDATE teachers SET available=False WHERE id = tec_id;
UPDATE courses SET teacher_id=tec_id WHERE teacher_id = 0;
RETURN NEW;
END
$$
LANGUAGE plpgsql;
The following output will appear if the function is created successfully in the postgres database.
Run the following SQL command to create a view of the courses table.
SELECT * FROM courses;
The following output will appear if the view is created successfully.
Now, run the following SQL statement to create the INSTREAD OF INSERT trigger named update_data that will be fired when a new record is inserted by using the create_view view.
INSTEAD OF INSERT ON courses_view
FOR EACH ROW
EXECUTE FUNCTION insert_course();
The following output will appear if the trigger is created successfully.
Run the following INSERT statement to insert a new record into the courses table by using courses_view view.
values ('Higher Math', 3.0);
The following output will appear if the data is inserted successfully using view.
Now, run the following SELECT statements to check how the courses and teachers tables have been modified after inserting a new record into the courses table by using view.
SELECT * FROM courses_view;
The following output shows that a new record has been inserted into the courses table, and the available field of the teachers’ table has been updated from “t” to “f,” where the id value is 3, and this id value has been updated in the courses table to assign this teacher for the newly inserted course.
Conclusion
Many database tasks can be done automatically by using a trigger in the PostgreSQL database. Each trigger is executed by using a particular function. Many purposes of using triggers have been explained in this tutorial by creating multiple triggers with the function. Three different types of triggers have been created here that will help the new PostgreSQL users to know the way of creating and using triggers in the PostgreSQL database.
