PostgreSQL – ALTER TRIGGER

In PostgreSQL, to modify the trigger, you use ALTER TRIGGER statement. This statement is a PostgreSQL extension of the SQL standard. The syntax of the ALTER TRIGGER statement is as follows:

Syntax:
ALTER TRIGGER trigger_name ON table_name
RENAME TO new_name;

Let’s analyze the above syntax:

  • First, specify the name of a trigger associated with a particular table that you want to change.
  • Second, put the new trigger name in the RENAME TO clause.

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 to modify the above-created trigger use the below statement:

ALTER TRIGGER username_check ON staff
RENAME TO check_username;

Output:

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :

Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.