Another common use case for event triggers is to prevent the execution of certain commands based on a specific condition. If you only want to stop users from executing some commands, you can always revoke the privileges using more conventional means. The triggers may come in handy if you want to do this based on a certain condition, let's say, time of the day:
CREATE OR REPLACE FUNCTION abort_create_table_func()
RETURNS event_trigger
AS
$$
DECLARE
current_hour int := extract(hour from now());
BEGIN
if current_hour BETWEEN 9 AND 16
then
RAISE EXCEPTION 'Not a suitable time to create tables';
end if;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE EVENT TRIGGER abort_create_table ON ddl_command_start
WHEN TAG IN ('CREATE TABLE')
EXECUTE PROCEDURE abort_create_table_func();The preceding code is, again, quite simple:
First, we create a trigger function that prevents a change if the current hour of the day is between 9 to 16.
We create a trigger...