Results 1 to 3 of 3
  1. #1
    Join Date
    May 2005
    Posts
    25

    Unanswered: trigger function not working!!!

    Hey all,

    My function which is called by a trigger only partially works!!!

    There are three triggers which are attached to three tables - one main table and two junction tables. When any records are inserted, deleted, or updated in any of these three tables it calls a function which records the changes in an audit table.

    The problem is, when deleting from the main table -the trigger does not fire!!! For all other operations and deleting from the junction table it works!

    Also the second problem is if i need to delete records from the main parent table and all its related records in the child table -the trigger will not fire when these records are deleted....although if a records is deleted by itself in the child table it works perfectly!!!

    Any ideas why this may happen and what could be done to resolve this???

    I am using postgresql 8.1.

    Thanks in advance for any help.

    Sub

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    For us to be able to do anything except make guesses, you are going to need to post your code...
    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 2005
    Posts
    25
    Hey loquin,

    Code as requested:

    Trigger on main table 'risk':

    CREATE TRIGGER risk_audit
    AFTER INSERT OR DELETE OR UPDATE on risk
    FOR EACH ROW EXECUTE PROCEDURE risk_changes_audit();

    Trigger on child tables:

    CREATE TRIGGER risk_audit2
    AFTER INSERT OR DELETE OR UPDATE on riskstep
    FOR EACH ROW EXECUTE PROCEDURE risk_changes_audit();

    CREATE TRIGGER risk_audit3
    AFTER INSERT OR DELETE OR UPDATE on riskcplan
    FOR EACH ROW EXECUTE PROCEDURE risk_changes_audit();

    Function called by trigger :
    Create or Replace Function risk_changes_audit () Returns Trigger AS $risk_audit$

    Begin

    IF (TG_OP = 'Delete' AND TG_RELNAME='risk') THEN
    Insert into risk_audit (aUser, aTName, aDate, aRiskid, aname, aRdesc , aRtype, aLikeHD, aRimpact, aRresp, aRlastRev, aRrevDets, aROwner, cReason) VALUES (user, TG_RELNAME ,now(),OLD.*, 'Risk deleted by user');
    Return OLD;

    ELSEIF (TG_OP='UPDATE' AND TG_RELNAME='risk') THEN
    INSERT into risk_audit (aUser, aTName, aDate, aRiskid, aname, aRdesc , aRtype, aLikeHD, aRimpact, aRresp, aRlastRev, aRrevDets, aROwner, cReason) VALUES (user, TG_RELNAME ,now(),OLD.*, 'Risk updated by user');
    Return OLD;

    ELSEIF (TG_OP='INSERT' AND TG_RELNAME='risk') THEN
    INSERT into risk_audit (aUser, aTName, aDate, aRiskid, aname, aRdesc , aRtype, aLikeHD,aRimpact, aRresp, aRlastRev, aRrevDets, aROwner, cReason) VALUES (user, TG_RELNAME ,now(),NEW.*, 'NEW RISK INSERTED BY USER');
    Return NEW;

    ELSEIF (TG_OP='DELETE' AND TG_RELNAME='riskstep') THEN
    INSERT into risk_audit (aUser, aTName, aDate, aRiskid, aRSStep, cReason) VALUES (user, TG_RELNAME ,now(),OLD.*, 'Step for risk deleted by user');
    Return OLD;

    ELSEIF (TG_OP='INSERT' AND TG_RELNAME='riskstep') THEN
    INSERT into risk_audit (aUser, aTName, aDate, aRiskid, aRSStep, cReason) VALUES (user, TG_RELNAME ,now(),new.*, 'New step has been added for risk');
    Return new;

    ELSEIF (TG_OP='DELETE' AND TG_RELNAME='riskcplan') THEN
    INSERT into risk_audit (aUser, aTName, aDate, aRiskid, aRCPlan, cReason) VALUES (user, TG_RELNAME ,now(),OLD.*, 'Contingency plan for risk deleted by user');
    Return OLD;

    ELSEIF (TG_OP='INSERT' AND TG_RELNAME='riskcplan') THEN
    INSERT into risk_audit (aUser, aTName, aDate, aRiskid, aRCPlan, cReason) VALUES (user, TG_RELNAME ,now(),new.*, 'New contingency plan has been added for risk');
    Return new;

    End if;

    Return NULL;

    END;

    $risk_audit$ LANGUAGE plpgsql;

    Any ideas???...

    It seems to be the trigger on delete for the risk table (main table) that doesnt work....

Posting Permissions

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