Open In App
Related Articles

PostgreSQL – Disabling a Trigger

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
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