Create a trigger in Postgres that will insert a record to another table with added column

Question

I have these 2 tables created in postgres,

CREATE TABLE EMPLOYEE(
   ID           INT             PRIMARY KEY NOT NULL,
   NAME         VARCHAR(100)    NOT NULL,
   ADDRESS      VARCHAR (250)   NOT NULL
);


CREATE TABLE HIST_EMPLOYEE(
   HISTORYNUM   INT             NOT NULL,
   ID           INT             NOT NULL,
   NAME         VARCHAR(100)    NOT NULL,
   ADDRESS      VARCHAR (250)   NOT NULL
);

..so whenever I want to insert, update, or delete a record in my EMPLOYEE table, that record should be inserted in HIST_EMPLOYEE table. regardless if it is single or multiple rows

..I know that there are lots of answers in this site..but the problem is I added a column in my HISTORY table HIST_EMPLOYEE..that's why I cant used this script that most of the answers posted,

insert into hist_employee select * from employee

...this is what i've started so far, but it has so many errors..

TRIGGER

CREATE TRIGGER insertHistory
    AFTER INSERT OR UPDATE ON employee   --MAIN TABLE
        EXECUTE PROCEDURE insertHistory('hist_employee');  --HISTORY TABLE

TRIGGER FUNCTION

CREATE OR REPLACE FUNCTION insertHistory() RETURNS TRIGGER AS 
$func$
    BEGIN
       EXECUTE format('insert into %I select * from %I', TG_ARGV[0] ,TG_TABLE_NAME );
       return null;
    END;
$func$ 
LANGUAGE plpgsql;

NOTE:

1. This trigger should be also applicable or flexible enough to be implemented on other tables with different set of columns.

2. HISTORYNUM column is unique per id,

3. And, is it possible to combine these 3, AFTER INSERT, AFTER UPDATE, BEFORE DELETE .. in one trigger ??

thanks to all who will respond..sorry if you find my question too vague

Answer 1

I think that the column historynum is too cumbersome to use. I would suggest replacing it by a timestamp. You can also add a column indicating the type of operation.

create table employee (
    id int primary key,
    name text not null,
    address text not null);

create table employee_history (
    id int,
    name text not null,
    address text not null,
    modified_at timestamp,
    operation text);

You have to use a trigger before to have access to the currently modified / removed row.

create or replace function trigger_on_employee()
returns trigger language plpgsql
as $function$
begin
    if tg_op = 'DELETE' then
        insert into employee_history
        select old.*, current_timestamp, tg_op;
        return old;
    else
        insert into employee_history
        select new.*, current_timestamp, tg_op;
        return new;
    end if;
end; $function$;

create trigger trigger_on_employee
  before insert or update or delete
  on employee
  for each row
  execute procedure trigger_on_employee();

Some tests:

insert into employee values 
(1, 'John', 'Berlin'),
(2, 'Adam', 'Paris'),
(3, 'Mike', 'London');

update employee
set name = 'Anna'
where id = 1;

delete from employee
where id = 2;

select * 
from employee_history
order by modified_at;

 id | name | address |        modified_at         | operation 
----+------+---------+----------------------------+-----------
  1 | John | Berlin  | 2015-12-20 16:26:35.703232 | INSERT
  2 | Adam | Paris   | 2015-12-20 16:26:35.703232 | INSERT
  3 | Mike | London  | 2015-12-20 16:26:35.703232 | INSERT
  1 | Anna | Berlin  | 2015-12-20 16:26:35.750609 | UPDATE
  2 | Adam | Paris   | 2015-12-20 16:26:35.761521 | DELETE
(5 rows)

Answer 2

1. There really isn't anything to be gained from overgeneralizing your trigger function, IMHO, especially one as short as this one. Keep it simple and write a new trigger function for each history table.
2. HISTORYNUM column? Never mind; I see it. Not sure what to do with it, though.
3. No. An individual trigger must be either BEFORE or AFTER. You can declare multiple triggers that call the same function, though.

With that out of the way, your trigger function as written is almost certain to run into the "Mutating Tables" error. Also, you appear to be trying to copy the entire contents of the "current" table into the "history" table every single time an update occurs. You already have the new data (for INSERT and UPDATE) and the old data (for UPDATE and DELETE) in the pseudorecords NEW and OLD respectively. Why not make use of that?

Beyond that, you say you have

so many errors..

Perhaps if you tell us some of them, we can help you fix them.