-2

I want to make a trigger that when a table, let's call it A, is updated, it counts the number of rows on A and updates a value in another table B, how can it be done?

Miguel Trigo
  • 1
  • 2
  • What you have tried till now show your efforts? – Ajay Pandya Mar 23 '16 at 07:27
  • I'm very new to pgsql, also very new to databases creation, I've been trying to create a trigger following the postgresql.org documentation but i dont have very clear how to create a procedure. – Miguel Trigo Mar 23 '16 at 08:00

1 Answers1

0

There is full sample here: http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html

To create stored procedure you are supposed to provide: CREATE FUNCTION your_function_name(parameters_goes_here) RETURNS returned_data_type_or_trigger_for_triggers AS string_value_with_body;

Normally semicolon ends command, so you can't type body. Other databases changes command delimiter. PG allows special string delimiter dollar signs. Please read more at this topic: What are '$$' used for in PL/pgSQL

Going back to counters - they are tricky. Let's say you count posts in topics. And moving posts between two topics. Since they have counters, topics are updated. That in turn means update lock. What would happen if there are two concurrent posts moved between two topics? First move locks his base topic and tries other one. And the same happens to other one. :D To deal with it you need to ensure that both of them tries with the same one. For instance sort topics by ID and pick first of them.

Community
  • 1
  • 1
Michał Zaborowski
  • 3,911
  • 2
  • 19
  • 39
  • That was the sample I was following, I made this code CREATE OR REPLACE FUNCTION contar_formar() RETURNS trigger AS $BODY$ BEGIN UPDATE equipos SET numeromiembros = (SELECT count(*) FROM formar) WHERE idequipo = (SELECT equipo FROM formar); END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION contar_formar() OWNER TO postgres; but its not working, when I try to insert something in formar, it says An error has occurred: ERROR: control reached end of trigger procedure without RETURN CONTEXT: PL/pgSQL function contar_formar() – Miguel Trigo Mar 23 '16 at 08:52
  • Solved, I had to add RETURN NULL before ending, thank you for your help! – Miguel Trigo Mar 23 '16 at 09:00