CODEWITHSUNDEEP
postgresqltriggersplpgsql
MitoCode
MitoCode

Reputation: 327

Inserted, Deleted tables in postgreSQL, like SQL Server?

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;

Upvotes: 6

Views: 3760

Answers (2)

user330315
user330315

Reputation:

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;

Upvotes: 3

mu is too short
mu is too short

Reputation: 434745

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.

Upvotes: 5

Related Questions