I have four tables A,B,C,D
C is dependent to B
B is dependent to A
User insert one record in A then
insert one record in B then
user enter 2 records into C.
I have one before insert trigger on C which it will compare the value with D and it's satisfied then it will allow to insert the value in C.
Else
it will delete the previous row inserted in C,B,A. I mean if user enter 1 record then the
2nd record is failed then i want to delete the previouly inserted record in C, in B, in C.
A
-----
service_id
service_details
primary_key(service_id)
B
-------
Service_id
calibration_id
calibration_details
cal_date
primary_key(service_id,calibration_id)
Foreign key(A#service_id)
C
-----
serivce_id
calibration_id
stanadard_id
standard_next_cal_date
Foregin_key(calibration_id,service_id)
D
----
standard_id
next_cal_date
Table D have these records
--------------------------------------
Freqstd 21-AUG-01
OM1106 31-DEC-00
So user insert a record in A
----------------------
insert into A
values('1','service for cal');
Row into B.
-------------------
insert into B
values('1','1','Cal purpose','16-AUG-01');
Rows insert into C
------------------------
insert into C
values('1','1','Freqstd','')
So my trigger take the next_cal_date(From D) into standard_next_cal_date and it will compare that date with cal_date(Table B), And if standard_next_cal_date is greater then that then it will insert into C.
In this case the standard_next_cal_date is 21-AUG-01 and cal_date is 16-AUG-01. So
the condition is satisfied and it will insert into table C.
Second Row
----------------------
insert into C
values('1','1','OM1106 ','')
In this case the standard_next_cal_date is 31-DEC-00 and cal_date is 16-AUG-01. So
the condition is failed, So now i raised the application error and i want to
delete the previous inserted records in C,B,A
My Trigger code is
CREATE OR REPLACE TRIGGER check_cal-date
BEFORE INSERT
ON SCOTT.C
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
select next_cal_date into :new.standard_next_cal_date from D
where standard=:new.standard and
next_cal_date >=(select cal_date from B
where service_id=:new.service_id and calibration_id=:new.calibration_id);
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,
'This equipment ||:new.equipment_id||' is out of cal');
scott.invalid_calibration(:new.Service_ID,:new.CALIBRATION_ID);
END;
/
For example if the standard don't have date or next_cal_date is less then cal_date(Table B) then user can't
insert record in the table C. The total service is invalid, So When no_data_found then i am raising application
error and i executing the invalid_calibration procedure. That procedure will delete all the rows from C,B,A
where the service_id,calibration_id equals to :new.service_id,:new.calibration_id.
My Procedure code is
CREATE OR REPLACE PROCEDURE SCOTT.Invalid_Calibration (
service_id_in IN SCOTT.C.Service_ID%type,
calibration_id_in IN SCOTT.C..Calibration_ID%type) AS
BEGIN
delete from C
where service_id = service_id_in and
calibration_id = calibration_id_in;
delete from B
where service_id = service_id_in and
calibration_id = calibration_id_in;
delete from A
where service_id = service_id_in;
EXCEPTION
WHEN OTHERS THEN
null;
commit;
END ;
/
So my problem is this is not working. I know that some where i am missing, But where i am not understanding.
If anybody have idea please let me know. Thanks.
