Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2007

    Unanswered: I have a function question

    I have this function to tell me when there have been updates, additions or deletions to my table. I would like to add to it the ability to display the info for that row prior to an update/delete and the info just after the update/delete. Can someone tell me what I need to change to make that happen?

    create function alexamara.updateMarinaHistory() returns trigger as $$
     theRecord record;
      if (TG_OP = 'UPDATE') then
    	  insert into alexamara.MarinaHistory (Action) values ('UPDATE');
      end if;
      if (TG_OP = 'DELETE') then
    	  insert into alexamara.MarinaHistory (Action) values ('DELETE');
      end if;
      if (TG_OP ='INSERT') then
    	  insert into alexamara.MarinaHistory (Action) values ('INSERT');
      end if;
      return null;
    $$ language 'plpgsql';
    I call the function with this:

    create trigger MarinaHistoryTrigger
     after UPDATE or INSERT or DELETE on alexamara.Marina
     for each row execute procedure alexamara.updateMarinaHistory();
    I'm also assuming I'll need to add the required column feilds to the table as well, right?

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    I don't see anything obviously wrong with your trigger.

    There doesn't appear to be the need for a declare of theRecord, as it isn't being used.

    Also, you may want to look at your capitalization. I normally use lower case only for objet names in PoatgreSQL. (if you use mixed case, you often need to quote the object names. i.e. alexamara."MarinaHistory"

    I would set up a table with, at minimum, the operation type (Your action field), the user who performed the operation, and a timestamp field.

    In that case, I would insert

    insert into alexamara.marinahistory (action, username, ts) values ('UPDATE', CURRENT_USER, now());
    "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