Thursday, September 5, 2013

Postgresql - Triggers on DDL Statements

PostgreSQL 9.3

Awaiting DDL Triggers i.e EVENT TRIGGERS  features  finally  come live with PostgreSQL 9.3

Yes!!.. now we can have a Trigger on DDL Statements like Oracle does.

Syntax                                                                                                                               

CREATE - EVENT TRIGGER 
CREATE EVENT TRIGGER trigger_name
  ON event
  [ WHEN condition IN (values)  ]
  EXECUTE PROCEDURE function_name()

event : ddl_command_startddl_command_end and sql_drop 

ddl_command_start
  •  Trigger function ( i.e function_name() ) would be called before execution of CREATE/ALTER/DROP command.
ddl_command_end
  • Trigger function ( i.e function_name() ) would be called after execution  of CREATE/ALTER/DROP command.
sql_drop 
  • Trigger function ( i.e function_name() ) would be called Just before "ddl_command_end" event triggered.
  • Only for the DROP commands
  • To view the list of objects deleted by pg_event_trigger_dropped_objects()

condition : tg_tag. ( It will return calling trigger command ) 
values : CREATE TABLE, CREATE FUNCTION complete list of values 
function_name : A normal pl/pgsql function with return type as event_trigger & it not needed to return any value.

Ex:
CREATE TRIGGER FUNCTION

CREATE OR REPLACE FUNCTION fn_ddl_trigger()
  RETURNS event_trigger
 LANGUAGE plpgsql
  AS $$
BEGIN
  RAISE EXCEPTION 'Current running DDL command is : % ', tg_tag;
END;
$$;

CREATE EVENT TRIGGER 1)  Simple  

CREATE EVENT TRIGGER ddl_trigger ON ddl_command_start
   EXECUTE PROCEDURE fn_ddl_trigger();

OUTPUT 1)

root@boss[~]#su postgres
postgres@boss:$psql
postgres=# create table table1 (empname varchar(200));
NOTICE:  Current running DDL command is: CREATE TABLE
CREATE TABLE


CREATE EVENT TRIGGER 2) With 'WHEN' condition
      
CREATE EVENT TRIGGER ddl_trigger ON ddl_command_start WHEN tg_tag in (ALTER_TABLE, DROP_TABLE)
EXECUTE PROCEDURE fn_ddl_trigger();


OUTPUT 2)  trigger function will get called only ALTER and DROP, not for CREATE TABLE, so no messgae to display.


root@boss[~]#su postgres
postgres@boss:$psql
postgres=# create table table2 (empname varchar(200));

CREATE TABLE
ALTER - EVENT TRIGGER

ALTER EVENT TRIGGER name DISABLE
ALTER EVENT TRIGGER name ENABLE [ REPLICA | ALWAYS ]
ALTER EVENT TRIGGER name OWNER TO new_owner
ALTER EVENT TRIGGER name RENAME TO new_name

DROP - EVENT TRIGGER

DROP EVENT TRIGGER [ IF EXISTS ] name





Post a Comment