CODEWITHSUNDEEP
sqlpostgresqltriggersddl
SMW
SMW

Reputation: 339

ALTER TRIGGER command in PostgreSQL

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?

Upvotes: 7

Views: 7563

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175726

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();

Upvotes: 1

e4c5
e4c5

Reputation: 53734

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;

Upvotes: 8

Related Questions