I want to create a trigger on a table called takes in postgresql to update a value in another table called student I'm trying to do it in the following way. But I'm getting an error that there is syntax error near "OLD". I don't understand whats wrong with this. This is my code:

CREATE OR REPLACE FUNCTION upd8_cred_func
      (id1 VARCHAR, gr1 VARCHAR,id2 VARCHAR, gr2 VARCHAR) 
      RETURNS void AS $$
 BEGIN
    IF  (id1=id2 and gr1 is null and gr2 is not null) THEN 
        update student set tot_cred = tot_cred + 6 where id = id1;
    END IF;
    RETURN;
 END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER upd8_cred
    AFTER UPDATE ON takes
    FOR EACH ROW
    EXECUTE PROCEDURE upd8_cred_func(OLD.id,OLD.grade,NEW.id,NEW.grade);
share|improve this question

1 Answer

up vote 6 down vote accepted

The trigger function cannot have any parameters, but in the trigger you still will have access to the OLD and NEW record:

http://www.postgresql.org/docs/9.1/interactive/trigger-definition.html :

The trigger function must be declared as a function taking no arguments and returning type trigger. (The trigger function receives its input through a specially-passed TriggerData structure, not in the form of ordinary function arguments.)

About the records passed to the trigger procedure, please see http://www.postgresql.org/docs/9.1/interactive/plpgsql-trigger.html :

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are: [...] NEW, [...] OLD [...]

share|improve this answer
thanks a lot...it works – dotslash Oct 11 '11 at 14:12

Your Answer

 
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.