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..
CREATE TRIGGER insertHistory AFTER INSERT OR UPDATE ON employee --MAIN TABLE EXECUTE PROCEDURE insertHistory('hist_employee'); --HISTORY TABLE
CREATE OR REPLACE FUNCTION insertHistory() RETURNS TRIGGER AS $func$ BEGIN EXECUTE format('insert into %I select * from %I', TG_ARGV ,TG_TABLE_NAME ); return null; END; $func$ LANGUAGE plpgsql;
- This trigger should be also applicable or flexible enough to be implemented on other tables with different set of columns.
HISTORYNUM column is unique per id,
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