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;