Re: 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> 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
>
>
>
>
>