CREATE TRIGGER

Creates a trigger. For details, see Triggers.

Syntax

CREATE TRIGGER [ IF NOT EXISTS ] [[database.]schema.]trigger
    ON SCHEDULE [[database.]schema.]schedule
    EXECUTE PROCEDURE procedure AS DEFINER

Parameters

IF NOT EXISTS

If an object with the same name exists, do not create it and proceed. If you omit this option and the object exists, Vertica generates a ROLLBACK error message. In both cases, the object is not created if it already exists.

The IF NOT EXISTS clause is useful for SQL scripts where you want to create an object if it does not already exist.

For related information, see ON_ERROR_STOP.

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

trigger

The name of the trigger.

schedule

The schedule with which to associate the trigger.

procedure

The function signature of the stored procedure.

AS DEFINER

The user to execute the stored procedure as. Currently, the only option is DEFINER, which executes the stored procedure as the definer of the trigger.

Privileges

Superuser

Examples

CREATE TRIGGER requires a schedule and stored procedure:

=> CREATE PROCEDURE revoke_all_on_table(table_name VARCHAR, user_name VARCHAR)
LANGUAGE PLvSQL
AS $$
BEGIN
    EXECUTE 'REVOKE ALL ON ' || QUOTE_IDENT(table_name) || ' FROM ' || QUOTE_IDENT(user_name);
END;
$$;

=> CREATE SCHEDULE 24_hours_later USING DATETIMES('2022-12-16 12:00:00');

To create the trigger with schedule 24_hours_later and stored procedure revoke_all_on_table() with arguments customer_dimension and Bob:

=> CREATE TRIGGER revoke_trigger ON SCHEDULE 24_hours_later EXECUTE PROCEDURE revoke_all_on_table('customer_dimension', 'Bob') AS DEFINER;;