I am trying to write a Perl Function for one of the databases I'm
building a web application for. This function is triggered to occur
BEFORE INSERT OR UPDATE. This function is complex in that it involves
fields in two different tables which need to be updated, where the
updates one receives depend on and must actually also reflect the same
data entered into one of the tables explicitly by the user.
I basically want to make sure that certain Same fields in two tables are
automatically updated with the exact Same information. This is redundant
but it is the design our engineers came up with and management is
forcing me to work with this redundant design.
I've been trying to I've been trying to tweak this function using "new."
& "old." prefixes for the fields that I'm manipulating but it doesn't
work. I've attached my function below. Any input on how this logic is
wrong is appreciated. See the code below.
CREATE FUNCTION classdata_scheduleid_sync()
-- the purpose of this function is to make sure that when a schedule_id
-- is updated in the registration_and_attendance table, that the
-- class_id start_date, & end date fields in this table are
-- automatically updated with the corresponding class_id, start_date,
-- end_date from the schedules table
RETURNS trigger
AS 'DECLARE
schedule_info RECORD;
BEGIN
IF length(new.schedule_id) = 0 -- IS THIS AN INSERT OR UPDATE?
THEN -- IS AN UPDATE FOR existing schedule_id IS NULL
SELECT INTO schedule_info *
FROM schedules WHERE id = old.schedule_id;
UPDATE registration_and_attendance
SET class_id = schedule_info.class_id,
start_date = schedule_info.start_date,
end_date = schedule_info.end_date
WHERE id = old.id;
ELSE -- must have been an INSERT
SELECT INTO schedule_info *
FROM schedules WHERE id = new.schedule_id;
INSERT INTO registration_and_attendance(class_id, start_date, end_date)
VALUES (schedule_info.class_id, schedule_info.start_date,
schedule_info.end_date)
WHERE schedule_id = new.schedule_id;
END IF;
RETURN new;
END;'
LANGUAGE 'plpgsql';
