Open In App

PostgreSQL – Enabling a Trigger

Improve
Improve
Like Article
Like
Save
Share
Report

A PostgreSQL trigger is a function that is invoked automatically whenever an event such as insert, update, or delete happens.PostgreSQL gives us an option to either enable or disable triggers. A disabled trigger does not fire when the triggering event occurs. We must enable it to make sure it fires when the event occurs.

In PostgreSQL, we use the  ALTER TABLE ENABLE TRIGGER statement to enable a trigger.

Syntax:
ALTER TABLE table_name
ENABLE TRIGGER trigger_name |  ALL;

If we analyze the above syntax:

  • We first specify the name of the table associated with the trigger that we want to enable.
  • Then , we specify the name of the trigger we need to use. We can also use ALL to enable all the triggers that are associated with the aforementioned table.

Example :

Suppose we have a trigger check_fullname_before_insert which is used to check whether the given employee name is empty or not and only insert when it is non-empty.

The checkempty function related to the trigger is defined as:

The check_fullname_before_insert  trigger is defined as follows :

In this function, we raise a notice and do not return anything when the entered employee name has length 0, but when it is not of 0 lengths, then the contents are inserted into the table.

Before enabling the trigger, the table looks like this:

The following query will enable the trigger on the employees table :

ALTER TABLE employees
ENABLE TRIGGER check_fullname_before_insert;

Suppose we use the following statement to test the trigger :

INSERT INTO employees (
 employee_id,
 full_name,
 manager_id
)
VALUES
 (21, '', NULL);

Output:

When we tried to insert the employee, the check_fullname_before_insert trigger was fired and as the length of the employee name was 0, nothing was inserted into the table and a notice was also issued.

Now, Suppose we have multiple triggers with different functionalities on the students table. The following query will enable all the triggers that exist on this table :

ALTER TABLE students
ENABLE TRIGGER ALL;

Last Updated : 01 Feb, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads