Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Trigger to archive deleted rows

Status
Not open for further replies.

GoldenEye4ever

Programmer
May 26, 2007
20
CA
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.
 
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]
Code:
[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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top