Delete row with update trigger

I have a table with two columns: name(text) and count(integer)

A new name is inserted in the table if it doesn't exist (with count=1), otherwise the counter is incremented. I have a similar function that instead decrements the counter. What I'd like to achieve is that once this counter reaches 0, it deletes the row. I thought of using a trigger for this:

CREATE TRIGGER fooTrigger
AFTER UPDATE OF count ON foo
BEGIN
  DELETE FROM foo WHERE count=0;
END;

The problem is that for some reason, when doing so the trigger seems to delete every single row.

Any tip about what could be the issue? I'm suspecting the problem could be related to using a trigger on the table itself but开发者_JAVA技巧 I certainly dont' know..

Thanks in advance.

A possible solution could be limiting the delete to the row that is updated. I assume that the name column is unique (or primary key). The following trigger is only examining the count column on the row that has been updated:

create trigger fooTrigger
after update of count on foo
for each row when new.count = 0 begin
    delete from foo where name = new.name;
end

EDIT

If your name column is not unique, you can limit the delete to the actual rowid (this syntax is limited to sqlit) This means that if you have two rows with the name 'hello world' and two different counts the delete will only affect the row that hits 0 first! But i still assume that the name column is unique, for good design practice :)

create trigger fooTrigger
after update of count on foo
for each row when new.count = 0 begin
    delete from foo where rowid = new.rowid;
end