Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...It's extraordinarily refreshing to see truly expert advice without having to wade through hipper than thou attitude..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > PostgreSQL Inc.: PostgreSQL Forum

Trigger to archive deleted rows

thread699-1610962
Read More Threads Like This One
GoldenEye4ever (Programmer)
6 Jul 10 19:08
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

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.
feherke (Programmer)
7 Jul 10 4:39
Hi

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 old;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;

Feherke.
http://free.rootshell.be/~feherke/

GoldenEye4ever (Programmer)
7 Jul 10 8:19
Thank you so much, it works now.
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To PostgreSQL Inc.: PostgreSQL

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close