PostgreSQL – Disabling a Trigger
In PostgreSQL, there is no specific statement such as DISABLE TRIGGER for disabling an existing trigger. However, one can disable a trigger using the ALTER TABLE statement as follows:
Syntax: ALTER TABLE table_name DISABLE TRIGGER trigger_name | ALL
Let’s analyze the above syntax:
- First, you specify the name of the trigger, which you want to disable, after the DISABLE TRIGGER keywords.
- To disable all triggers associated with a table, you use ALL instead of a particular trigger name.
Example:
First, we create a staff table for demonstration with the below statement:
CREATE TABLE staff( user_id serial PRIMARY KEY, username VARCHAR (50) UNIQUE NOT NULL, password VARCHAR (50) NOT NULL, email VARCHAR (355) UNIQUE NOT NULL, created_on TIMESTAMP NOT NULL, last_login TIMESTAMP );
Second, create a function that validates the username of a staff. The username of staff must not be null and its length must be at least 8.
CREATE FUNCTION check_staff_user() RETURNS TRIGGER AS $$ BEGIN IF length(NEW.username) < 8 OR NEW.username IS NULL THEN RAISE EXCEPTION 'The username cannot be less than 8 characters'; END IF; IF NEW.NAME IS NULL THEN RAISE EXCEPTION 'Username cannot be NULL'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
Third, create a new trigger on the staff table to check the username of a staff. This trigger will fire whenever you insert or update a row in the staff table.
CREATE TRIGGER username_check BEFORE INSERT OR UPDATE ON staff FOR EACH ROW EXECUTE PROCEDURE check_staff_user();
Now we can disable the username_check triggered using the below statement:
ALTER TABLE staff DISABLE TRIGGER username_check;
Output: