up vote 0 down vote favorite

I have the following trigger:

CREATE OR REPLACE FUNCTION record_deleted_item() RETURNS TRIGGER AS $$
BEGIN
    IF (select number_itens from grup where NEW.gruop_id = group_id) != 0 THEN
    UPDATE gruop SET number_itens= (number_itens-1) WHERE gruop_id=NEW.gruop_id;
   END IF;
RETURN NEW;
END; $$
LANGUAGE plpgsql;

CREATE TRIGGER deleted_item
BEFORE DELETE ON item
FOR EACH ROW EXECUTE PROCEDURE record_deleted_item();

In my if-else clause I should check if the column value number_items from group table is not 0. How could I check it? The way I've done returns me an error.

share|improve this question
86% accept rate
1  
Do you really have both grup and gruop tables or is that a typo? – mu is too short Dec 13 '11 at 20:53
feedback

2 Answers

up vote 0 down vote accepted

SELECT it into variable and compare to zero. You may also want to check if SELECT actually returned anything.

DECLARE
    ni bigint;
BEGIN
    SELECT number_itens INTO ni FROM ...;
    IF NOT FOUND THEN
    END IF;
    IF NI != 0 THEN
    END IF;
END
share|improve this answer
Thank you, It's working now! – dextervip Dec 13 '11 at 20:28
You're welcome. – Michael Krelin - hacker Dec 13 '11 at 20:30
feedback
up vote 1 down vote

I don't think you need a conditional in there at all. If there is nothing in grup that matches NEW.gruop_id = group_id when the UPDATE just won't do anything at all so there is no need for two queries:

CREATE OR REPLACE FUNCTION record_deleted_item() RETURNS TRIGGER AS $$
BEGIN
    UPDATE gruop SET number_itens = (number_itens - 1) WHERE gruop_id = NEW.gruop_id;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

The above assumes that grup is just a typo and you only have a gruop table.

Also, if this really is a DELETE trigger then you'll want to work with OLD instead of NEW:

CREATE OR REPLACE FUNCTION record_deleted_item() RETURNS TRIGGER AS $$
BEGIN
    UPDATE gruop SET number_itens = (number_itens - 1) WHERE gruop_id = OLD.gruop_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;
share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.