Hello everyone,
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();