CODEWITHSUNDEEP
postgresqlfunctiontriggers
Shea Lutton
Shea Lutton

Reputation: 71

Postgres trigger works for INSERT, not for DELETE

I am having a problem with a trigger. I created a trigger and a function to keep track of how many rows are in use by each user in my DB. The INSERT part of the trigger and function work correctly, but the DELETE does nothing. When I insert rows in my app, the rowcount goes up, when I delete, the rowcount does not change.

Here is the TABLE where I keep the row count:

Table "public.rowcount"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 user__id   | integer | not null
 table_name | text    | not null
 total_rows | bigint  |

Here is my TRIGGER:

CREATE TRIGGER countrows_m_time
  AFTER INSERT OR DELETE on m_time
  FOR EACH ROW EXECUTE PROCEDURE count_rows_m_time();

And here is the FUNCTION:

CREATE OR REPLACE FUNCTION count_rows_m_time()
RETURNS TRIGGER AS
'  
   BEGIN
      IF TG_OP = ''INSERT'' THEN
        UPDATE rowcount
          SET total_rows = total_rows + 1
          WHERE table_name = TG_RELNAME
          AND user__id = (SELECT user__id from vi_m_time_users where m_value_id = NEW.m_value_id);
      ELSIF TG_OP = ''DELETE'' THEN
         UPDATE rowcount
            SET total_rows = total_rows - 1
            WHERE table_name = TG_RELNAME
            AND user__id = (SELECT user__id from vi_m_time_users where m_value_id = OLD.m_value_id);
      END IF;
      RETURN NULL;
   END;
' LANGUAGE plpgsql;

Any ideas? Many thanks, Shea

Upvotes: 1

Views: 21511

Answers (3)

Archmal
Archmal

Reputation: 63

CREATE OR REPLACE FUNCTION count_rows_m_time()
RETURNS TRIGGER AS $count_rows_m_time$
 DECLARE
   BEGIN
      IF (TG_OP = 'INSERT') THEN
        UPDATE rowcount
          SET total_rows = (total_rows + 1)
          WHERE table_name = TG_RELNAME
          AND user_id = (SELECT user_id FROM vi_m_time_users WHERE m_value_id = NEW.m_value_id);
      ELSIF (TG_OP = 'DELETE') THEN
         UPDATE rowcount
            SET total_rows = (total_rows - 1)
            WHERE table_name = TG_RELNAME
            AND user_id = (SELECT user_id FROM vi_m_time_users WHERE m_value_id = OLD.m_value_id);
      END IF;
      RETURN NULL;
   END;
$count_rows_m_time$ LANGUAGE plpgsql;

*i think so

Upvotes: 0

Shea Lutton
Shea Lutton

Reputation: 71

After my original post, I went back to simplify the problem. I split the TRIGGER and FUNCTION into separate INSERT and DELETE activities. Both the INSERT TRIGGER and FUNCTION continue to work correctly as an AFTER trigger. So I have excluded it from the post. Here is a simplified problem with the DELETE trigger.

Here is my new TRIGGER:

CREATE TRIGGER remrows_m_int
  BEFORE DELETE on m_int
  FOR EACH ROW EXECUTE PROCEDURE rem_rows_m_int();

And here is my new FUNCTION:

CREATE OR REPLACE FUNCTION rem_rows_m_int()
RETURNS TRIGGER AS
'
   BEGIN
      IF TG_OP = ''DELETE'' THEN
         UPDATE rowcount
            SET total_rows = total_rows - 1
            WHERE table_name = TG_RELNAME
            AND user__id = (SELECT user__id from vi_m_int_users where result_id = OLD.result__id);
      END IF;
      RETURN OLD;
   END;
' LANGUAGE plpgsql;

This trigger is now working if I remove rows on the m_int table. The problem with the AFTER trigger was never resolved, but using a BEFORE with a RETURN OLD seems to be an ok substitute. By hard coding certain variables, the issue was related to the use of the OLD.result__id in the function.

Upvotes: 2

Andrew Lazarus
Andrew Lazarus

Reputation: 19330

Returning NULL cancels the INSERT/DELETE operations. (You can use it for referential integrity too complicated to be enforced with a simple constraint.)

You want to return OLD from the DELETE and NEW from the INSERT.

Upvotes: 1

Related Questions