Skip to content
Related Articles

Related Articles

PostgreSQL – Enabling a Trigger
  • Last Updated : 01 Feb, 2021
GeeksforGeeks - Summer Carnival Banner

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;

Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up
Recommended Articles
Page :