473,223 Members | 1,728 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,223 software developers and data experts.

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 2098

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Rajesh Kapur | last post by:
I have a master slave configuration on linux machines running MySQL 4.0.21. Once every hour, a process deletes about 9000 rows and re-inserts fresh data on the master. The master process completes...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
12
by: Bob Stearns | last post by:
This is probably the wrong forum for this, but I thought it might start some discussion. The INSERT statement, in its current form, has problems being formatted so a human reader can follow it....
8
by: Carl | last post by:
Hi, I hope someone can share some of their professional advice and help me out with my embarissing problem concerning an Access INSERT query. I have never attempted to create a table with...
4
by: Mike Hnatt | last post by:
My goal is to get data from an XML file into a couple of tables in an Access database. The XML file is a little complex so I need control over what I do (I can't just read it into a dataset). ...
1
by: Primillo | last post by:
'Full source 'Insert, delete and update don't work Public Class WebForm1 Inherits System.Web.UI.Page Protected WithEvents Button1 As System.Web.UI.WebControls.Button Protected WithEvents...
20
by: Mark Harrison | last post by:
So I have some data that I want to put into a table. If the row already exists (as defined by the primary key), I would like to update the row. Otherwise, I would like to insert the row. I've...
1
by: Dragon | last post by:
I am using mysql with the InnoDB engine. I wrote a perl script that first selects something from a table, and then updates a second table based on the select from the first table. I need to make...
8
by: nano2k | last post by:
Hi Shortly, I keep invoices in a table. Occasionally, someone will fire the execution of a stored procedure (SP) that performs several UPDATEs against (potentially) all invoices OLDER than a...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.