Subject: Insert/Update Perl Function involving two tables needing to by 'in
	sync'
From: Ferindo Middleton Jr <fmiddleton@verizon.net>
Reply-To: fmiddleton@verizon.net
To: pgsql-sql@postgresql.org
Content-Type: text/plain
Organization: Sleek Collar
Message-Id: <1099452361.1738.27.camel@scnmaster.sleekcollar.com>
Mime-Version: 1.0
Date: Tue, 02 Nov 2004 22:26:01 -0500
Content-Transfer-Encoding: 7bit

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';