Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2010
    Posts
    2

    Unanswered: Trigger to archive deleted rows

    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.

  2. #2
    Join Date
    May 2008
    Posts
    277
    A few points:

    1) For some reason, you're passing arguments to your trigger function that you're not using.

    2) I'd personally make the trigger an AFTER trigger. That way it's only run if the row is actually updated or deleted.

    3) I'd also make life much easier by lowercasing all names, eliminating the need for quotes.

    Code:
    CREATE TRIGGER move_to_history
    AFTER UPDATE OR DELETE ON test
    FOR EACH ROW EXECUTE PROCEDURE backup_test()
    Having said that, offhand I don't see anything wrong with your code. Posting the exact error message would be helpful.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    here's a great example with almost exactly what you are attempting to accomplish.
    PostgreSQL: Documentation: Manuals: PostgreSQL 8.1: Trigger Procedures
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •