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 $$
if (TG_OP = 'UPDATE') then
insert into alexamara.MarinaHistory (Action) values ('UPDATE');
if (TG_OP = 'DELETE') then
insert into alexamara.MarinaHistory (Action) values ('DELETE');
if (TG_OP ='INSERT') then
insert into alexamara.MarinaHistory (Action) values ('INSERT');
$$ 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?
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