Sql Trigger Trouble

Question

Hey guys i cant get this trigger to work, ive worked on it for an hour or so and cant see to figure out where im going wrong, any help would be appreciated

CREATE OR REPLACE TRIGGER allergy

 BEFORE INSERT ON   

 DECLARE 
 med VARCHAR2(20);

 BEGIN

 SELECT  v.medication RCD.specify
 INTO  med
 FROM  visit v, relcondetails RCD
 WHERE :new.medication = v.medication AND RCD.specifiy = 'allergies';

 IF med = allergies THEN
  RAISE_APPLICATION_ERROR(-20000, 'Patient Is alergic to this medication');
 END IF;
END allergy;

When put into oracle

ERROR at line 6: ORA-04079: invalid trigger specification

Answer 1

BEFORE INSERT ON <TABLE NAME>

and why select both v.medication and RCD.specify when you're only selecting into one variable?

Answer 2

You've probably seen this but: http://msdn.microsoft.com/en-us/library/aa258254(SQL.80).aspx

CREATE TRIGGER TriggerName
ON MyTableName
FOR MyEvent
AS
     -- My Trigger Logic

Answer 3

In addition to Mark's point that you are missing the table name, and Martin's point that you want this to be a row-level trigger, your actual body won't compile, for a couple of reasons.

• You look like you're trying to select two columns, but you don't have a comma between them, and you only have one local variable in the INTO clause
• You use an identifier allergies which is not declared anywhere.

I also doubt that your query is logically correct, but of course I don't know the database design so I can't say for sure.

Answer 4

CREATE OR REPLACE TRIGGER allergy BEFORE INSERT ON   

name of table here

FOR EACH ROW -- forgot this too

DECLARE 
    med VARCHAR2(20);

You should really be declaring this as %type.

    med visit.medication%type;

 BEGIN

 SELECT  v.medication RCD.specify

Requires a comma between columns

    INTO  med

Two columns need two variables

    FROM  visit v, relcondetails RCD
    WHERE :new.medication = v.medication AND RCD.specifiy = 'allergies';

You have no join condition between your two tables, that's very bad. This query will perform a Cartesian between the two tables and then return all of them that have 'allergies' and :new.medication in their respective columns.

you also probably need a filter condition to limit the query to a particular patient or a particular visit. This query will do it for all patients and all their visits squared.

  IF med = allergies THEN

I don't know what /allergies/ is in this IF. There's no variable that's defined as that and without quotes it's not a string.

    RAISE_APPLICATION_ERROR(-20000, 'Patient Is alergic to this medication');

This error message reinforces what I said about your query. You think you're querying for a single patient but you're not.

  END IF;
END allergy;

Seriously, if you're writing software to save a person from getting potentially life threatening medication then please consider some other line of work. I swear I'm not saying this to be rude, but your code sample shows almost no understanding of the pl/sql language or sql or any scrap of programming background. I think you started with some sample code and tried to modify it into something something. But you're really left with gibberish. I'm starting to think this is homework.