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

["Campbell, Lance" <lance@xxxxxxxxxxxx>]

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@xxxxxxxxxxxxxx]
Sent: Wednesday, November 16, 2016 5:48 PM
To: Campbell, Lance <lance@xxxxxxxxxxxx>
Cc: pgsql-admin@xxxxxxxxxxxxxx
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@xxxxxxxxxxxx> 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