Re: table - on delete - how to insert a record into a different table

I figured it out. I needed to:

RETURN OLD;

not

RETURN NULL;

Thanks for your help.

Lance

From: pgsql-admin-owner(at)postgresql(dot)org [mailto:pgsql-admin-owner(at)postgresql(dot)org] On Behalf Of Campbell, Lance
Sent: Wednesday, November 16, 2016 6:15 PM
To: Bear Giles <bgiles(at)coyotesong(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: [ADMIN] table - on delete - how to insert a record into a different table

I have this almost working. When the trigger performs it inserts the value into the table delete_file. But the row is not deleted from the table XYZ.

I believe the issue is in my return type?

CREATE OR REPLACE FUNCTION delete_file_fn()
RETURNS trigger AS '
BEGIN
INSERT INTO delete_file (file_location)VALUES(to_char(OLD.created_timestamp,''YYYY'')||''/''|| to_char(OLD.created_timestamp,''MM'') || ''/'' || to_char(OLD.created_timestamp,''DD'') || ''/'' || OLD.id || OLD.ext);
RETURN NULL;
END; ' language plpgsql;

Thanks,

Lance

From: Bear Giles [mailto:bgiles(at)coyotesong(dot)com]
Sent: Wednesday, November 16, 2016 5:48 PM
To: Campbell, Lance <lance(at)illinois(dot)edu<mailto:lance(at)illinois(dot)edu>>
Cc: pgsql-admin(at)postgresql(dot)org<mailto:pgsql-admin(at)postgresql(dot)org>
Subject: Re: [ADMIN] table - on delete - how to insert a record into a different table

Add a TRIGGER with an action on DELETE.

On Wed, Nov 16, 2016 at 4:45 PM, Campbell, Lance <lance(at)illinois(dot)edu<mailto:lance(at)illinois(dot)edu>> wrote:
Postgresql: 9.5

I have two tables xyz and delete_file:

CREATE TABLE xyz
(
id integer NOT NULL DEFAULT nextval(('xyz_id_seq'::text)::regclass),
name character varying DEFAULT ''::character varying,
ext character varying,
created_timestamp timestamp with time zone DEFAULT now(),
CONSTRAINT xyz_pkey PRIMARY KEY (id),
CONSTRAINT xyz_fk_id_fkey FOREIGN KEY (fk_id)
REFERENCES abc (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE
)
WITH (
OIDS=FALSE
);

CREATE TABLE delete_file
(
id integer NOT NULL DEFAULT nextval(('delete_file_id_seq'::text)::regclass),
file_location text
)
WITH (
OIDS=FALSE
);

When a row is deleted from the table xyz I want to insert a row into the table delete_file using an insert statement similar to this with the values from the row to be deleted.

INSERT INTO delete_file (file_location) (select to_char(created_timestamp,’YYYY’)||’/’|| to_char(created_timestamp,’MM’) || ‘/’ || to_char(created_timestamp,’DD’) || ‘/’ || id || ‘.’ || ext FROM xyz);

What is the best way to do this?

Thanks,

Lance