SELECT CASE to use NOT EXISTS in Oracle failing
<p>I have been trying to find a solution to use an If_Exists() style statement in Oracle PL SQL. I am trying to create a trigger which checks to see if a certain airsoft gun exists in the guns table when a member tries to input a new gun owned in the gunsOwned table. If the gun does not exist in the guns table, then it must be inputted to the table before the gun owned is inputted to the gunsOwned table or it will violate referential integrity as the Make and Model in gunsOwned are foreign keys to the Make and Model in the Guns table. However I keep getting Trigger created with compilation errors, and all of my attribute names are correct, so don't know why the select case statement is not working. Here is the code:</p>

<pre><code>CREATE TRIGGER updateGuns 
BEFORE INSERT ON GunsOwned  
FOR EACH ROW
DECLARE
   MemberAddingGun NUMBER;
   NewMake VARCHAR2(30);
   NewModel VARCHAR2(30);
BEGIN
   MemberAddingGun := :NEW.OwnerID;
   NewMake := :NEW.MakeOwned;
   NewModel := :NEW.ModelOwned;
   SELECT CASE gunExists
    WHEN NOT EXISTS(SELECT Make, Model FROM Guns WHERE Make=NewMake AND Model=NewModel)
   THEN 
    INSERT INTO Guns VALUES(NewMake, NewModel);
   END
   UPDATE Member
   SET NumOfGuns = NumOfGuns+1
   WHERE MemberID = MemberAddingGun;
END updateGuns;
.
RUN;
</code></pre>

<p>Could anyone help? </p>

<p>Thanks!</p>