Trigger to archive deleted rows

[GoldenEye4ever]

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

CREATE TABLE "TEST"
(
  "name" character varying(50) NOT NULL,
  "SIN" character varying(14) NOT NULL,
  CONSTRAINT test_pk PRIMARY KEY ("SIN")
)

test_history table

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

CREATE TRIGGER "moveToHistory"
  BEFORE DELETE
  ON "TEST"
  FOR EACH ROW
  EXECUTE PROCEDURE "BACKUP_TEST"('name', 'SIN');

backup_test trigger function

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]

Hi
[ul]
[li]Quoted identifiers are case sensitive, unquoted identifiers are not. OLD == old != "OLD" ( PostgreSQL | SQL Syntax | Lexical Structure | Identifiers and Key Words )[/li]
[li]If a trigger returns [tt]null[/tt], cancels the operation. The [tt]new[/tt] record in case of [tt]delete[/tt] statement is [tt]null[/tt]. ( PostgreSQL | Triggers | Overview of Trigger Behavior )[/li]
[/ul]

[b]CREATE[/b] [b]OR[/b] [b]REPLACE[/b] [b]FUNCTION[/b] [green][i]"BACKUP_TEST"[/i][/green][teal]()[/teal]
  RETURNS [b]trigger[/b] [b]AS[/b]
$BODY$
BEGIN
    [b]INSERT[/b] [b]INTO[/b] [green][i]"TEST_HISTORY"[/i][/green]
    [teal]([/teal]
        [green][i]"SIN"[/i][/green][teal],[/teal]
        [green][i]"name"[/i][/green][teal],[/teal]
        [green][i]"change_date"[/i][/green]
    [teal])[/teal]
    [b]VALUES[/b]
    [teal]([/teal]
        OLD[teal].[/teal][green][i][highlight]"[/highlight]SIN[highlight]"[/highlight][/i][/green][teal],[/teal]
        OLD[teal].[/teal]name[teal],[/teal]
        NOW[teal]()[/teal]
    [teal]);[/teal]
    [b]RETURN[/b] [highlight]old[/highlight][teal];[/teal]
END[teal];[/teal]
$BODY$
  [b]LANGUAGE[/b] [green][i]'plpgsql'[/i][/green] VOLATILE
  COST [purple]100[/purple][teal];[/teal]

Feherke.

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

 

[GoldenEye4ever]

Thank you so much, it works now.