1

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

  • Is HISTORYNUM and employee(id) able to be serials? – dizzystar Dec 20 '15 at 22:29
  • HISTORYNUM field should have a default value – Sahap Asci Dec 21 '15 at 9:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.