using OLD variable in DELETE trigger

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Rick Casey

    #1

    using OLD variable in DELETE trigger

    Hello,

    I am trying to get a trigger to fire when a record is deleted that will
    record the deleted record in a history file. Here is the trigger code:


    CREATE OR REPLACE FUNCTION logPedigreesDel () RETURNS TRIGGER AS '
    begin
    INSERT INTO PEDIGREES_hist(
    PedigreeID,
    ProjectID,
    prev_PedID,
    prev_FamIndID,
    prev_FamID,
    prev_IndID,
    prev_FatherID,
    prev_MotherID,
    prev_Sex,
    prev_Age,
    prev_Dna,
    prev_Diagnosis,
    prev_StatusBroa d,
    prev_StatusNarr ow,
    prev_LiabilityC lass,
    prev_Exclude,
    prev_ModDate,
    prev_ModBy,
    InsertDate)
    VALUES (
    OLD.PedigreeID,
    OLD.ProjectID,
    OLD.PedID,
    OLD.FamIndID,
    OLD.FamID,
    OLD.IndID,
    OLD.FatherID,
    OLD.MotherID,
    OLD.Sex,
    OLD.Age,
    OLD.Dna,
    OLD.Diagnosis,
    OLD.StatusBroad ,
    OLD.StatusNarro w,
    OLD.LiabilityCl ass,
    OLD.Exclude,
    OLD.ModDate,
    OLD.ModBy,
    OLD.InsertDate) ;
    end;
    ' LANGUAGE plpgsql;

    However, here is the error message when the trigger fires:

    psql:testphdtri g.sql:1: ERROR: record "old" is not assigned yet
    DETAIL: The tuple structure of a not-yet-assigned record is indeterminate.
    CONTEXT: PL/pgSQL function "logpedigreesde l" line 2 at SQL statement

    OLD is supposed to be an automatically created variable, datatype
    record, and about a dozen examples I've seen use the OLD variable in
    this way without having to declare anything.

    Can anyone tell me what I'm doing wrong?

    thanks mucho,
    Rick
    --
    Rick Casey, Research Associate
    Institute for Behavioral Genetics
    rick.casey@colo rado.edu
Working...