GoldenEye4ever
Programmer
Hi, I'm new to PostgreSQL. I've been programming in PL/SQL for quite some time now and would really appreciate some help on this.
I have 2 tables:
- test
- test_history
Whenever a row is updated or deleted from the test table, I need to insert its OLD value (the value prior to making the update or deletion) into the test_history table.
In Oracle I'd just create a trigger, then either directly insert into the test_history table within the trigger, or pass the values (OLD.name, etc...) into a procedure which would then insert the passed row data into the test_history table.
I've tried to create a trigger in PostgreSQL, but I wasn't able to get it to work:
test table
test_history table
moveToHistory trigger
backup_test trigger function
The code seems to compile...but whenver I delete a row from the test table, I just get an error message and the insert + delete fail.
The idea with this is to expand this to also include all updates, thus a full history of all changes made to the TEST table in the would be kept in the TEST_HISTORY table.
I have 2 tables:
- test
- test_history
Whenever a row is updated or deleted from the test table, I need to insert its OLD value (the value prior to making the update or deletion) into the test_history table.
In Oracle I'd just create a trigger, then either directly insert into the test_history table within the trigger, or pass the values (OLD.name, etc...) into a procedure which would then insert the passed row data into the test_history table.
I've tried to create a trigger in PostgreSQL, but I wasn't able to get it to work:
test table
Code:
CREATE TABLE "TEST"
(
"name" character varying(50) NOT NULL,
"SIN" character varying(14) NOT NULL,
CONSTRAINT test_pk PRIMARY KEY ("SIN")
)
test_history table
Code:
CREATE TABLE "TEST_HISTORY"
(
"name" character varying(50) NOT NULL,
"SIN" character varying(14) NOT NULL,
change_date time without time zone NOT NULL,
CONSTRAINT test_history_pk PRIMARY KEY (change_date, "SIN")
)
moveToHistory trigger
Code:
CREATE TRIGGER "moveToHistory"
BEFORE DELETE
ON "TEST"
FOR EACH ROW
EXECUTE PROCEDURE "BACKUP_TEST"('name', 'SIN');
backup_test trigger function
Code:
CREATE OR REPLACE FUNCTION "BACKUP_TEST"()
RETURNS trigger AS
$BODY$
BEGIN
INSERT INTO "TEST_HISTORY"
(
"SIN",
"name",
"change_date"
)
VALUES
(
OLD.SIN,
OLD.name,
NOW()
);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION "BACKUP_TEST"() OWNER TO postgres;
The code seems to compile...but whenver I delete a row from the test table, I just get an error message and the insert + delete fail.
The idea with this is to expand this to also include all updates, thus a full history of all changes made to the TEST table in the would be kept in the TEST_HISTORY table.