Каскадное обновление связных таблиц Oracle+Delphi [D7]
Страницы:
<
1
2
>
Geo © (2011-06-11 16:32:00)
Я сам никогда не сталкивался с БД, с работой через ADO,
новичек. с делфи все обстоит немного лучше.
Проблема в следующем
Код
//проверка уникальности и не 0 и нашелся в базе и не
равен текущему
if
((Login_Form.QPatient.Locate('id_patient',id_pat_copy,[loCaseInsensitive]))
and (id_patient<>id_pat_copy)) then
begin
ShowMessage('Пациент с таким кодом уже
существует');
exit;
end;
begin
if
(id_patient=StrToInt(EIdPat.Text)) then
begin
ind_copy:=true //создать нового пациента;
end
else
id_patient:=StrToInt(EIdPat.Text);
with Login_form.QPatient do
begin
if ind_copy
then
begin
Locate('id_patient',id_pat_copy,[loCaseInsensitive]);
Edit;
end
else
Append;
FieldValues['id_patient']:=id_patient;
FieldValues['id_plot']:=id_plot;
FieldValues['id_doctor']:=id_doctor;
FieldValues['id_policlinic']:=id_policlinic;
FieldValues['id_speciality']:=id_speciality;
FieldValues['id_street_reg']:=id_street_reg;
FieldValues['id_street_home']:=id_street_home;
FieldValues['id_ed_inst']:=id_ed_inst;
FieldValues['name']:=Pname;
FieldValues['surname']:=surname;
FieldValues['patronymic']:=patronymic;
FieldValues['DOB']:=DOB;
FieldValues['insurance_org']:=insurance_org;
FieldValues['insurance_num']:=insurance_num;
FieldValues['house_reg']:=house_reg;
FieldValues['house_home']:=house_home;
FieldValues['father']:=father;
FieldValues['mother']:=mother;
FieldValues['sisters']:=sisters;
FieldValues['brothers']:=brothers;
FieldValues['SNILS']:=SNILS;
if num_card
<>'' then
FieldValues['num_card']:=num_card;
FieldValues['document_type']:=document_type;
FieldValues['invalidity']:=invalidity;
FieldValues['dipans_uch']:=dipans_uch;
Post;
end;
////*******************************start******************************
//Diary-каскадное обновление
with
Login_form.QPatDiary do
begin
Sql.Clear;
Sql.Add('Select * from diary_survey where
id_patient='+IntToStr(id_pat_old));
Open;
if FieldValues['id_diary']<>null then
begin
First;
Edit;
while not EOF
do//Цикл обновления по всем дневникам пациента
begin
Edit;
FieldValues['id_patient']:=id_patient;
FieldValues['id_plot']:=id_plot;
FieldValues['id_doctor']:=id_doctor;
FieldValues['id_policlinic']:=id_policlinic;
FieldValues['id_speciality']:=id_speciality;
FieldValues['id_street_reg']:=id_street_reg;
FieldValues['id_street_home']:=id_street_home;
FieldValues['id_ed_inst']:=id_ed_inst;
Post;
Next;
end;
end;
end;
///******************************end**********************************
// льготы
with Login_form.QBenefits do
begin
Sql.Clear;
Sql.Add('Select * from BENEFITS where
id_patient='+IntToStr(id_pat_old));
Open;
if FieldValues['id_benefit']<>null then
begin
First;
Edit;
while not EOF
do//Цикл обновления по всем льготам пациента
begin
Edit;
FieldValues['id_patient']:=id_patient;
FieldValues['id_plot']:=id_plot;
FieldValues['id_doctor']:=id_doctor;
FieldValues['id_policlinic']:=id_policlinic;
FieldValues['id_speciality']:=id_speciality;
FieldValues['id_street_reg']:=id_street_reg;
FieldValues['id_street_home']:=id_street_home;
FieldValues['id_ed_inst']:=id_ed_inst;
Post;
Next;
end;
end;
SQL.Clear;
SQL.Add('SELECT * FROM BENEFITS');
end;
with
Login_form.QPatient do
begin
if not ind_copy then //если создавалась
копия
begin
//удалям старую запись
Locate('id_patient',id_pat_old,[loCaseInsensitive]);
Delete;
end;
end;
end;
На выделенном Post, если мы не меняем id_patient, но меняем
id_plot срабатывает триггер
Geo (2011-06-11 16:33:00) [21]
DECLARE NUMROWS INTEGER;
BEGIN
/* ERwin Builtin Trigger */
/* Patients Diary_survey on parent update
restrict */
/* ERWIN_RELATION:CHECKSUM="000d657c", PARENT_OWNER="",
PARENT_TABLE="Patients"
CHILD_OWNER="", CHILD_TABLE="Diary_survey"
P2C_VERB_PHRASE="", C2P_VERB_PHRASE="",
FK_CONSTRAINT="R_10",
FK_COLUMNS="id_patient""id_plot""id_doctor""id_policlinic""id_speciality""
id_street_reg""id_street_home""id_ed_inst" */
IF
/* %JoinPKPK(:%Old,:%New," <> "," OR ") */
:old.id_patient <> :new.id_patient OR
:old.id_plot <> :new.id_plot OR
:old.id_doctor <> :new.id_doctor OR
:old.id_policlinic <> :new.id_policlinic
OR
:old.id_speciality <> :new.id_speciality
OR
:old.id_street_reg <> :new.id_street_reg
OR
:old.id_street_home <> :new.id_street_home
OR
:old.id_ed_inst <> :new.id_ed_inst
THEN
SELECT count(*) INTO NUMROWS
FROM Diary_survey
WHERE
/*
%JoinFKPK(Diary_survey,:%Old," = "," AND") */
Diary_survey.id_patient =
:old.id_patient AND
Diary_survey.id_plot =
:old.id_plot AND
Diary_survey.id_doctor =
:old.id_doctor AND
Diary_survey.id_policlinic =
:old.id_policlinic AND
Diary_survey.id_speciality =
:old.id_speciality AND
Diary_survey.id_street_reg =
:old.id_street_reg AND
Diary_survey.id_street_home =
:old.id_street_home AND
Diary_survey.id_ed_inst =
:old.id_ed_inst;
IF (NUMROWS > 0)
THEN
/* ********************************MY COD************
*/
/* UPDATE Diary_survey SET
Diary_survey.id_plot=:new.id_plot WHERE
Diary_survey.id_patient=:new.id_patient;
/* ********************************END OF MY COD************
*/
raise_application_error(
-20005,
'Cannot update Patients because
Diary_survey exists.'
);
END IF;
END IF;
Тригер сгенерирован Erwin, схема в прикрепленном файле
если я пишу то, что помечено как /*
********************************MY COD************ , то срабатывает
другой триггер целостности Diary_survey.
Как лучше поступить в данном случае, сорри за длинный пост,
просто выложил, чтобы была видна полная картина
Схема -
http://www.imageup.ru/img55/dlya-sql678942.png
Страницы:
<
1
2
>
Наверх 
ЧАТ |
Орешник |
|
