Postgres trigger works for INSERT, not for DELETE

Question

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

The trigger seems ok to me. Maybe the problem is somewhere else? I guess that the inner select returns nothing because of some foreign key or other trigger? Could you give the schemas of all tables involved? Or just try change the trigger to run BEFORE DELETE. — strkol
– strkol, Commented Mar 27, 2012 at 21:27
Well that is easy enough to try. I will give it a shot and report the results. — Shea Lutton
– Shea Lutton, Commented Mar 27, 2012 at 21:34
OT: I'd recommend dollar quoting ($$ ... $$) for the function body to avoid quoting problems. — mu is too short
– mu is too short, Commented Mar 27, 2012 at 21:37
Changing the TRIGGER to a BEGIN instead of AFTER does not work. Now neither the INSERT or DELETE portion of the function works. Could this have anything to do with the RETURN part of the function? — Shea Lutton
– Shea Lutton, Commented Mar 27, 2012 at 23:12

Shea Lutton · Accepted Answer · 2012-03-29 16:49:32Z

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.

Andrew Lazarus · Accepted Answer · 2012-03-29 04:28:27Z

1

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.

answered Mar 29, 2012 at 4:28

Andrew Lazarus

19.5k3 gold badges39 silver badges57 bronze badges

1 Comment

strkol Over a year ago

Wrong. Returning NULL will cancel the rest of the triggers, but only if the trigger is declared as BEFORE TRIGGER (row-level). In this case the trigger is declared as AFTER, so your statement is wrong.

Archmal · Accepted Answer · 2013-11-17 16:33:17Z

0

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

answered Nov 17, 2013 at 16:33

Archmal

631 silver badge8 bronze badges

Collectives™ on Stack Overflow

Postgres trigger works for INSERT, not for DELETE

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related