CODEWITHSUNDEEP
postgresqltriggersmigrationfirebird
Fábio Amorim
Fábio Amorim

Reputation: 103

How to migrate a Firebird's Trigger to PostgreSQL

I'm migrating an entire database from Firebird to PostgreSQL and it's not rocket science. But I'm having serious trouble with triggers. Specially the Firebird's POSITION argument.

Actually, I'm searching about the POSITION behavior. I need it but in PostgreSQL.

Those are the Triggers in Firebird:

This Trigger needs to be executed first:

/* Trigger: TRG_CFE_ESTOQUE_PROCESSADO */
CREATE OR ALTER TRIGGER TRG_CFE_ESTOQUE_PROCESSADO FOR ITENS_CFE
 BEFORE UPDATE POSITION 0
AS                                                                                                                  
BEGIN                                                                                                               
  IF(NEW.ITE_QTD <> OLD.ITE_QTD)THEN                                                                                
  BEGIN                                                                                                             
    NEW.ITE_ESTOQUE_PROCESSADO = 'N';                                                                             
  END                                                                                                               
END

And this one needs to be executed after:

/* Trigger: TRG_CFE_ESTOQUE_EXCLUIDO */
CREATE OR ALTER TRIGGER TRG_CFE_ESTOQUE_EXCLUIDO FOR ITENS_CFE
 BEFORE DELETE POSITION 1
AS                                                                                                                  
BEGIN                                                                                                               
  UPDATE ITENS_CFE                                                                                                  
     SET ITE_ESTOQUE_PROCESSADO = 'N'                                                                             
   WHERE PRO_CODIGO             = OLD.PRO_CODIGO                                                                    
     AND CFE_CODIGO             = OLD.CFE_CODIGO;                                                                   
END

For now, I'm not testing it, just searching for a way to reproduce the expected behavior.

Upvotes: 1

Views: 571

Answers (2)

J Spratt
J Spratt

Reputation: 2012

The standard way I've defined trigger would be like the following:

CREATE OR REPLACE FUNCTION func_table_x_after_insert()
RETURNS TRIGGER
AS $$
BEGIN
    INSERT INTO table_y
        (id)
    VALUES
        (NEW.id)
    ;
    RETURN NEW;
END;
$$  LANGUAGE PLPGSQL;

CREATE TRIGGER trig_table_x_after_insert
AFTER INSERT ON table_x
FOR EACH ROW EXECUTE PROCEDURE func_table_x_after_insert();

The function you define can handle multiple steps.

Upvotes: 1

F&#225;bio Amorim
F&#225;bio Amorim

Reputation: 103

Searching again, I've found something in the PostgreSQL Documentation:

If multiple triggers of the same kind are defined for the same event, they will be fired in alphabetical order by name

And I think it will do the magic.

But is this the best way of doing it?

Upvotes: 1

Related Questions