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
Add a TRIGGER with an action on DELETE.
On Wed, Nov 16, 2016 at 4:45 PM, Campbell, Lance <lance@xxxxxxxxxxxx> wrote:
