Inserted, Deleted tables in postgreSQL, like SQL Server?

Question

I want to create a trigger after a inserted event, but I need the data that I inserted in order to register into a new table for my trigger in PostgreSQL

In SQL Server I capture these values from the Inserted or deleted pseudo tables but do these tables also exists in PostgreSQL? Or what can I do?

This is my trigger code

CREATE TRIGGER tri_compago
  AFTER INSERT
  ON matricula
  FOR EACH ROW
  EXECUTE PROCEDURE fn_insCompPago();

CREATE OR REPLACE FUNCTION fn_insCompPago()
  RETURNS trigger AS
$BODY$
  DECLARE
  BEGIN

   insert into compromisopago(codigotasa,descripcion,precio,fechavencimiento,codigomatricula)
   select codigotasa,descripcion,precio,fechavencimiento,i.codigo
   from programacionpago pp join inserted i on isnull(i.codigoconvenio,0) = isnull (pp.codigoconvenio,0)
   and pp.codigopresentacion = i.codigopresentacion
   where pp.vigencia = 1 and i.vigencia = 1;

   RETURN NULL;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fn_insCompPago()
  OWNER TO postgres;

mu is too short · Accepted Answer · 2013-05-14 01:37:51Z

5

I have no idea how triggers work in SQL Server but in PostgreSQL, you use the OLD and NEW special variables:

NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is NULL in statement-level triggers and for DELETE operations.

OLD
Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is NULL in statement-level triggers and for INSERT operations.

So you probably want to look at NEW.codigo, NEW.codigoconvenio, NEW.codigopresentacion, and NEW.vigencia in your case. You'd probably replace the i.vigencia = 1 part of the WHERE clause with a simple IF i.vigencia = 1 conditional as well.

answered May 14, 2013 at 1:37

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user330315 · Accepted Answer · 2013-05-14 06:58:34Z

3

A trigger defined as for each row is fired - well - for each row in Postgres. SQL Server does not support row level triggers, only statement level triggers.

Inside a row level trigger you always deal with exactly one row with the old and new values accessible (very roughly comparable to "inserted" and "deleted" virtual tables in SQL Server)

You can specify under which name you want to reference those records, the default is new and old (as mu is too short has already explained).

So as the values you are interested in are available as "scalar" values, you don't need any join to do your insert:

insert into compromisopago
  (codigotasa,descripcion,precio,fechavencimiento,codigomatricula)
select codigotasa,
       descripcion,
       precio,
       fechavencimiento,
       new.codigo
from programacionpago pp 
where pp.vigencia = 1 
  and i.vigencia = 1;
  and pp.codigoconvenio = new.codigoconvenio
  and pp.codigopresentacion = new.codigopresentacion;

answered May 14, 2013 at 6:58

user330315

1 Comment

Chris Travers Over a year ago

Technically (and this is a nitpick) you have two rows, old and new ;-)

Collectives™ on Stack Overflow

Inserted, Deleted tables in postgreSQL, like SQL Server?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related