Open In App

PostgreSQL – Disabling a Trigger

Improve
Improve
Like Article
Like
Save
Share
Report

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:


Last Updated : 28 Aug, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads