Re: Problems With DELETE

Hello List-

I'm having some problems in my program that I've been spending the day
going over and over and am now going to ask for some help, as I cannot see
the problem, but am hoping some seasoned sets of eyes will!

I have some VIEWS that need to get updated when the user chooses to delete
data from them from within my program. Most views are working, but it seems
that one is not.

Here's one that IS working:

CREATE OR REPLACE VIEW
"elementdata"."personnel_info"
AS
SELECT
pi.projectname, pi.people_id,
pi.elementid, p.last_name, p.first_name,
p.job_title, p.business_email, p.business_phone,
p.cell_phone, p.pager_no
FROM
elementdata.data_personnel_info pi
INNER JOIN projectdata.people p USING (projectname, people_id);

CREATE RULE
delete_personnel_info
AS ON DELETE TO
elementdata.personnel_info
DO INSTEAD DELETE FROM
elementdata.data_personnel_info
WHERE
data_personnel_info.elementid = old.elementid
AND
data_personnel_info.people_id::text = old.people_id::text;

CREATE RULE
update_personnel_info
AS ON UPDATE TO
elementdata.personnel_info
DO INSTEAD

(
--update data_personnel_info
UPDATE
elementdata.data_personnel_info
SET
projectname = new.projectname,
people_id = new.people_id
WHERE
((data_personnel_info.elementid = old.elementid)
AND
((data_personnel_info.people_id)::text = (old.people_id)::text));

--update shown projectdata.people fields
UPDATE
projectdata.people
SET
last_name = new.last_name,
first_name = new.first_name,
job_title = new.job_title,
business_email = new.business_email,
business_phone = new.business_phone,
cell_phone = new.cell_phone,
pager_no = new.pager_no
WHERE
(((people.projectname)::text = (old.projectname)::text)
AND
((people.people_id)::text = (old.people_id)::text));

);

And now, for my 'measurement view', which is NOT working; when the user
chooses delete from the program, it's not deleting it from the database.
And if I run the statement in pgAdmin: delete from elementdata.measurement
where elementid = 18700; (a row in the measurement table of my database) -
it's telling me that it did something: "Query returned successfully: 1 rows
affected, 703 ms execution time.", but if I query the table based on that
elementid (select * from elementdata.measurement where elementid = 18700;),
it's still in the table...

Here are my CREATE, DELETE, and UPDATE statements for the 'measurement'
view:

Create view "elementdata"."measurement" As
SELECT e.elementid, da.projectname, da.square_unit AS square_unit_sq,
a.area * su.units_per_sqfoot AS area_sq, e.slope AS slope_inches,
sa.slopearea * cu.units_per_cufoot AS slopearea_sq, da.linear_unit AS
linear_unit_lin, (p.perimeter * lu.units_per_foot::double precision) AS
perimeter_lin, da.cubic_unit AS cubic_unit_cu, e.height * lu.units_per_foot
AS height_lin, e.height::double precision * a.area * cu.units_per_cufoot AS
volume_cu, da.drawingid
FROM
((((((((((SELECT perimeter.elementid, perimeter.perimeter
FROM elementdata.perimeter
UNION
SELECT length.elementid, length.length AS perimeter
FROM elementdata.length)
UNION
SELECT circumference.elementid, circumference.circumference AS perimeter
FROM elementdata.circumference) p
LEFT JOIN elementdata.area a USING (elementid))
LEFT JOIN element e USING (elementid))
LEFT JOIN elementdata.slopearea sa USING (elementid))
JOIN layer la USING (layerid))
JOIN drawing da USING (drawingid))
JOIN globaldata.linear_units lu USING (linear_unit))
JOIN globaldata.square_units su USING (square_unit))
JOIN globaldata.cubic_units cu USING (cubic_unit));>

CREATE OR REPLACE RULE
delete_measurement
AS ON DELETE TO
elementdata.measurement
DO INSTEAD DELETE FROM
elementdata.data_measurement
WHERE
((data_measurement.elementid = old.elementid)
AND
(element.elementid = old.elementid));

CREATE RULE update_measurement AS ON UPDATE
TO elementdata.measurement
DO INSTEAD (UPDATE element SET height = (new.height_lin / (SELECT
linear_units.units_per_foot FROM globaldata.linear_units WHERE
((linear_units.linear_unit)::text = (old.linear_unit_lin)::text))), slope =
new.slope_inches WHERE (element.elementid = old.elementid); UPDATE drawing
SET linear_unit = new.linear_unit_lin, square_unit = new.square_unit_sq,
cubic_unit = new.cubic_unit_cu WHERE (drawing.drawingid = old.drawingid); );

Any thoughts/ideas would be greatly appreciated - I'm stuck as to why it's
working on all the views in the schema, but one... If you need anymore info
from me, just let me know.

Thanks in advance for your input and ideas!!
-Jeanna