I hope someone could point me in the right direction because I have been scratching my head on the best approach.
Using Cuba we have been able to create a basic workflow management app (50 users). These “jobs” can be recurring ,happening every year/month (even though recurring, still distinct records). Instead of manually recreating these jobs we would like to have this happen automatically when one of the jobs is closed. Meaning when a job goes from open to closed if the job has a recurring check mark create a new job using some of the original entries from the last job.
I originally was going to use a trigger in PostgreSQL to create new record, but ran into a road block because our jobs table has a one FK relationship to a forms composition table with a one-to-many relationship. Meaning I would also need to recreate multiple rows in the forms table and admittedly got stuck.
I started to think maybe there is probably an easier way and started to looking through the Cuba manual. We are on 7.1.4.
Could we use an onbeforeupdate listener to create a new record if certain conditions are met?
Any suggestions would be greatly appreciated.
This is the trigger function that works (without the composition table part though )
--New Function CREATE OR REPLACE FUNCTION recurringJob() RETURNS trigger AS $$ BEGIN IF new."status" = 'Closed' and old."status" = 'Open' and new."recurring_job"=true THEN INSERT INTO public.samplerecurringjobs_job ( "id", "uuid", "version", status, customer_name, job_description, recurring_job, frequency_id ) VALUES ( New.id+1, uuid_generate_v4(), 1, 'Open', NEW."customer_name", NEW."job_description", true, NEW."frequency_id" ); END IF; RETURN NEW; END $$ LANGUAGE plpgsql; ALTER FUNCTION recurringJob() --DROP TRIGGER trigger_recurring_job ON "samplerecurringjobs_job"; CREATE TRIGGER trigger_recurring_job AFTER UPDATE OF status ON "samplerecurringjobs_job" FOR EACH ROW EXECUTE PROCEDURE recurringJob();