PostgreSQL – ALTER TRIGGER
Last Updated :
28 Aug, 2020
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:
Like Article
Suggest improvement
Share your thoughts in the comments
Please Login to comment...