470,614 Members | 1,438 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

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

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
0 2007

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Rajesh Kapur | last post: by
16 posts views Thread by Philip Boonzaaier | last post: by
12 posts views Thread by Bob Stearns | last post: by
1 post views Thread by Primillo | last post: by
20 posts views Thread by Mark Harrison | last post: by
8 posts views Thread by nano2k | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.