Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Before Insert Trigger Don't perform the insert if the condition is true

706640Dec 20 2009 — edited Dec 20 2009
I have the following trigger that is correct but I found that the new row is being inserted if the condition is true, so I am missing a piece of logic. This is for a Graduate project, it is not a real prescription database but the point is there, I don't want the prescription ordered if a conflict exists! Thanks!
Here is the trigger:
create or replace
trigger drug_conflict_trigger
before insert on patient_prescriptions
for each row

declare
c_conflict varchar(100);
Begin
Select c.description into c_conflict from conflicts c, patient_prescriptions pp, current_medications cm
where pp.patient_id = cm.patient_id
and pp.drug_id = c.drug_id
and cm.drug_id = c.cannot_take_with;

dbms_output.put_line('conflict found: prescription not ordered');

--if the conflict is not found, it is an exception, but also the condition we need to order the prescription
exception when no_data_found then
dbms_output.put_line('Prescription ordered');
end;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 17 2010
Added on Dec 20 2009
7 comments
2,182 views