ALTER TRIGGER command in PostgreSQL

Question

I have an existed trigger in a Postgres 9.3 database, which is defined as follows:

CREATE TRIGGER trig
  AFTER UPDATE OR DELETE
  ON tab2
  FOR EACH ROW
  EXECUTE PROCEDURE proc3();

I want to change the trigger to be:

AFTER INSERT OR UPDATE OR DELETE

The manual on ALTER TRIGGER doesn't explain how to do it.

I want to change the trigger without dropping it. Is that possible?

Answer 1

Sorry, it's not possible to alter a trigger in this way. an OR REPLACE clause does not exists for triggers. However this is rarely a problem because in postgresql DDL statements can be wrapped in a transaction.

BEGIN;
DROP TRIGGER IF EXISTS trig on tab2;
CREATE TRIGGER trig
  AFTER INSERT OR UPDATE OR DELETE
  ON tab2
  FOR EACH ROW
  EXECUTE PROCEDURE proc3();

COMMIT;

Answer 2

The upcoming version of PostgreSQL will support CREATE OR REPLACE syntax:

CREATE OR REPLACE TRIGGER will either create a new trigger, or replace an existing trigger

To replace the current definition of an existing trigger, use CREATE OR REPLACE TRIGGER, specifying the existing trigger's name and parent table. All other properties are replaced.

  CREATE OR REPLACE TRIGGER trig
  AFTER INSERT OR UPDATE OR DELETE
  ON tab2
  FOR EACH ROW
  EXECUTE PROCEDURE proc3();