By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,512 Members | 644 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,512 IT Pros & Developers. It's quick & easy.

Insert/Update Perl Function involving two tables needing to by 'in sync'

P: n/a
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';
Jul 19 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.