Results 1 to 7 of 7
  1. #1
    Join Date
    May 2006
    Posts
    14

    Unanswered: trigger functions in postgreSQL

    Hi,
    I am new to postgreSQL, so i don't 've much idea abt creating triggers. I need to create a trigger that would set the isExpired value to 'T' if the startdate equals the enddate, each time there's an update on the specified table.
    here's what i did:

    create function set_subscriptionExpired() returns trigger as'
    begin
    if(channelsubscription.startDate = channelsubscription.endDate)
    then
    set channelsubscription.isExpired = ''T'';
    end if;
    end;
    ' language 'plpgsql';

    CREATE TRIGGER "checkSubscriptionExpired" AFTER UPDATE
    ON ChannelSubscription FOR EACH STATEMENT
    EXECUTE PROCEDURE public.set_subscriptionExpired();

    i get the following error when i try to update the channelsubscription table:

    ERROR: missing FROM-clause entry for table "channelsubscription"
    CONTEXT: SQL statement "SELECT (channelsubscription.startDate = channelsubscription.enddate)"
    PL/pgSQL function "set_subscriptionexpired" line 2 at if

    Can sumone plz let me know how to get arnd this problem. Thanks!

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Ref http://www.postgresql.org/docs/8.1/s...l-trigger.html

    I'm by no means a pg trigger expert, but you define the table used in the trigger definition, not inside the trigger function. Within the trigger function, use OLD or NEW as the field qualifier. (In an update, for instance, you could check OLD.Field against NEW.Field to see if it changed)

    SO, instead of
    Code:
    if(channelsubscription.startDate = channelsubscription.endDate) 
    then
    set channelsubscription.isExpired := ''T'';
    end if;
    try
    Code:
    if(NEW.startDate = NEW.endDate) then
      NEW.isExpired := 'T';
    end if;
    (Also note the Pascal assignment syntax (:= instead of =)
    Last edited by loquin; 05-12-06 at 14:23.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2006
    Posts
    14
    Thankyou for your reply. I did solve my previous problem, but now i get another error when i try to update my channelsubscription table.

    ERROR: control reached end of trigger procedure without RETURN
    CONTEXT: PL/pgSQL function "set_subscriptionexpired"

    I am not sure how to deal with returnin a value, i declared the return type as trigger, but i don't know what exactly to return here.. Really appreciate it if u or someone culd help me with this...Thnx!

  4. #4
    Join Date
    May 2006
    Posts
    14
    Nvermind... the link u send helped...thnx again!

  5. #5
    Join Date
    May 2006
    Posts
    14

    Red face trigger doesnot log changes

    Hi again,

    I want to create a trigger to updates the boolean value 'adexpired' in my table when adcurrent = adtarget (both are integer types). I created the following trigger function, however it doesn't seem to work. the change on adexpired is not reflected back to the table.

    CREATE OR REPLACE FUNCTION ad_expired()
    RETURNS "trigger" AS
    $BODY$begin
    if(new.adcurrent = new.adtarget) then
    new.adExpired := 'T';
    return new;
    end if;
    return new;
    end;$BODY$
    LANGUAGE 'plpgsql' VOLATILE;


    CREATE TRIGGER ad_expired
    AFTER UPDATE
    ON adcontract
    FOR EACH ROW
    EXECUTE PROCEDURE ad_expired();

    I 've the same problem with my previous trigger as well...
    Can someone plz tell me wut i m doin wrong..i can't fig out why its not workin..thnx!

  6. #6
    Join Date
    May 2006
    Posts
    14
    For triggers fired after an update or insert, the return value is ignored. To make the trigger work i simply had to change after update condition to before update..

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    and that makes sense, because AFTER the row has been updated, there IS no new value...
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •