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_sched uleid_sync()
-- the purpose of this function is to make sure that when a schedule_id
-- is updated in the registration_an d_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.sche dule_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_an d_attendance
SET class_id = schedule_info.c lass_id,
start_date = schedule_info.s tart_date,
end_date = schedule_info.e nd_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_an d_attendance(cl ass_id, start_date, end_date)
VALUES (schedule_info. class_id, schedule_info.s tart_date,
schedule_info.e nd_date)
WHERE schedule_id = new.schedule_id ;
END IF;
RETURN new;
END;'
LANGUAGE 'plpgsql'; 0 2127 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 processing under a minute. The
updates arrive on the slave within seconds. However, the slave takes about
15-20 minutes to post the same updates. Both master and slave have InnoDB
tables. The master and the slave are almost identical in capacity...
|
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 must be UPDATED, if not, they
must be INSERTED.
Logically then, I would like to SELECT * FROM <TABLE>
WHERE ....<Values entered here>, and then IF FOUND
UPDATE <TABLE> SET .... <Values entered here> ELSE
INSERT INTO <TABLE> VALUES <Values...
|
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. In particular, for a table with many
columns, the only to put the values in close proximity with the column
names is horizontally, which can lead to lines of 1000s of characters in
length which are difficult to read and edit. What I would like to...
|
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 one-to-one relationship but on this
occasion
I must keep username/password details within a seperate table.
Here's the basic specs and database schema:
-------------------------------------------
|
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).
The way I have it now is ennumerating through my XML file and for each
record, running an INSERT INTO SQL statement to put the values in the
database.
Is this going to be okay with 50 or so records (hitting the database with 50
or so INSERT...
| |
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 Button2 As
System.Web.UI.WebControls.Button
Protected WithEvents Button3 As
System.Web.UI.WebControls.Button
|
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 been doing something like
delete from foo where name = 'xx';
insert into foo values('xx',1,2,...);
|
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 sure that
there is no read or write to the tables while my script performs the
insert and update.
I looked at <http://dev.mysql.com/doc/refman/4.1/en/lock-tables.html>
and it says this:
...
|
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 date that is supplied to the SP as a parameter.
The SP is usually a lengthy process (it takes at least 30 mins).
The problem is that SQL server 2000 Dev Edition doesn't allow me to
insert new invoices that are "younger", while the SP is executing....
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |