title: Insert, Update, Delete Trigger Function to audit a table
body: <p>I want to create a trigger function to audit the <strong>table tb_register</strong>:</p>
  <pre><code>    CREATE TABLE olympic.tb_register (
        athlete_id    CHARACTER(7) NOT NULL,
        round_number  INT NOT NULL,
        discipline_id INT NOT NULL,
        register_date  DATE NOT NULL DEFAULT CURRENT_DATE,
        register_position INT DEFAULT NULL,
        register_time     TIME DEFAULT NULL,
        register_measure  REAL DEFAULT NULL,
        CONSTRAINT pk_register PRIMARY KEY (athlete_id, round_number, discipline_id),
        CONSTRAINT fk_register_athlete FOREIGN KEY (athlete_id) REFERENCES olympic.tb_athlete (athlete_id),
        CONSTRAINT fk_register_round FOREIGN KEY (discipline_id, round_number) REFERENCES olympic.tb_round (discipline_id, round_number)
      );
  </code></pre>
  <p>The function should upload all UPDATE, INSERT or DELETE actions to the table <strong>tb_athlete`s_info_log</strong> with the following structure of attributes that come from other tables.</p>
  <pre><code>CREATE TABLE IF NOT EXISTS olympic.tb_athletes_info_log (
    athlete_id CHARACTER VARYING (7) NOT NULL,
    discipline_id INT NOT NULL,
    round_number INT NOT NULL,
    athlete_name CHARACTER VARYING (50) NOT NULL,
    discipline_name  CHARACTER VARYING (50) NOT NULL,
    mark  CHARACTER VARYING (12) NOT NULL,
    rating INT NOT NULL,
    info_log_dt DATE, 
    CONSTRAINT PK_tb_athletes_info_log PRIMARY KEY(athlete_id,discipline_id,round_number),
    CONSTRAINT fk_athlethes_info_log_register FOREIGN KEY (athlete_id, round_number, discipline_id) REFERENCES olympic.tb_register(athlete_id, round_number, discipline_id)
  );
  </code></pre>
  <h2>This is my code:</h2>
  <pre><code>CREATE OR REPLACE FUNCTION fn_athletes_info() 
  RETURNS TRIGGER AS $$
      DECLARE
            updated_athlete_id tb_register.athlete_id%type;
            updated_discipline_id tb_register.discipline_id%type;
            updated_round_number tb_register.round_number%type;
            updated_athlete_name tb_athlete.name%type;
            updated_discipline_name  tb_discipline.name%type;
            updated_mark  tb_register.register_measure%type; 
            updated_rating tb_register.register_position%type;
            updated_info_log_dt tb_register.register_ts%type; 
      BEGIN
          
          IF (TG_OP = 'DELETE') THEN 
      
              updated_athlete_id = OLD.tb_register.athlete_id;
              updated_discipline_id = OLD.tb_register.discipline_id;
              updated_round_number = OLD.tb_register.round_number;
              updated_athlete_name = OLD.tb_athlete.name;
              updated_discipline_name = OLD.tb_discipline.name;
              updated_mark = OLD.tb_register.register_measure;
              updated_rating = OLD.tb_register.register_position;
              updated_info_log_dt = OLD.tb_register.register_date;
              
           ELSIF (TG_OP = 'UPDATE') THEN 
           
              updated_athlete_id = NEW.tb_register.athlete_id;
              updated_discipline_id = NEW.tb_register.discipline_id;
              updated_round_number = NEW.tb_register.round_number;
              updated_athlete_name = NEW.tb_athlete.name;
              updated_discipline_name = NEW.tb_discipline.name;
              updated_mark = NEW.tb_register.register_measure;
              updated_rating = NEW.tb_register.register_position;
              updated_info_log_dt = NEW.tb_register.register_date;
              
          ELSIF (TG_OP = 'INSERT') THEN 
          
              updated_athlete_id = NEW.tb_register.athlete_id;
              updated_discipline_id = NEW.tb_register.discipline_id;
              updated_round_number = NEW.tb_register.round_number;
              updated_athlete_name = NEW.tb_athlete.name;
              updated_discipline_name = NEW.tb_discipline.name;
              updated_mark = NEW.tb_register.register_measure;
              updated_rating = NEW.tb_register.register_position;
              updated_info_log_dt = NEW.tb_register.register_date;
              
          END IF; 
          -- Insert or update table with the new values.
          &lt;&lt;insert_update&gt;&gt;
          LOOP 
              BEGIN 
                  INSERT INTO tb_athletes_info_log (
                              athlete_id,
                              discipline_id,
                              round_number,
                              athlete_name,
                              discipline_name,
                              mark,
                              rating,
                              info_log_dt)
                           
                      VALUES (
                              updated_athlete_id,
                              updated_discipline_id,
                              updated_round_number,
                              updated_athlete_name,
                              updated_discipline_name,
                              updated_mark,
                              updated_rating,
                              updated_info_log_dt
                              );
                  
                  EXIT insert_update; 
              EXCEPTION
                  WHEN UNIQUE_VIOLATION THEN
                      -- do nothing
              END;
          END LOOP insert_update; 
          
          RETURN NULL; 
          
      END;
  $$ LANGUAGE plpgsql;
  
  CREATE TRIGGER tg_athletes_info
  AFTER INSERT OR UPDATE OR DELETE ON tb_register
      FOR EACH ROW EXECUTE FUNCTION fn_athletes_info();
  </code></pre>
  <p>When I try to INSERT some data to try the tigger:</p>
  <pre><code>BEGIN;
  INSERT INTO  tb_athlete  VALUES(7777777,'xxxxxx','XXX');
  INSERT INTO tb_register  VALUES(7777777,0,7,'2022-06-02 00:00:00',NULL,NULL);
  COMMIT;
  </code></pre>
  <p>I revceive the following:</p>
  <pre><code>ERROR:  missing FROM-clause entry for table &quot;tb_register&quot;
  LINE 1: SELECT NEW.tb_register.athlete_id
  </code></pre>
  <p>I got stuck when trying to understand how to convert the data into a foramt required by the table <strong>tb_athlete`s_info_log</strong></p>
  <p>Can anyone guide me thowards a solution for this?</p>
  <p>Thank you,
  A.</p>