Oracle INSERT Triggers With IF

Question

I am trying to make a trigger but get an error. Can someone explain to me what I am doing wrong, I will be more than grateful. It's for part of my college final project.

CREATE OR REPLACE TRIGGER NewTest_CaseEvent_Trigger
AFTER
INSERT
ON TESTRESULTS
FOR EACH ROW
DECLARE
    NEW_CASEID        Integer;
    RESULT            Char(3);

BEGIN
    RESULT = :new.Result;

    IF :new.PUIID IS IN (SELECT PUIID FROM CASE)THEN
       SELECT CASEID INTO NEW_CASEID 
       FROM CASE
       WHERE PUIID = :new.PUIID;

       INSERT INTO  CASEEVENT
       (CASEEVENTID, CASEID, EventDate, EventComments, MethodOfCommunication, CreatedBy, CreationDate, LastUpdateBy, LastUpdateDate)
           VALUES
           (CASEEVENTID_PK.NextVal, New_CaseID, :new.Date, 'New Test Result Available: include here the test result',
               Null, 'NewTest_CaseEvent_Trigger',SYSDATE,Null,Null);

     ELSEIF RESULT = 'PST' THEN
         INSERT INTO CASE
         (CASEID, PUIID, ActivationDate, ClosingDate, ClosingReason, ExposureTypeID, CaseWorkerID, CreatedBy,CreationDate, LastUpdateBy, LastUpdateDate)
         VALUES
         (CASEID_PK.NextVal, :new.PUIID, SYSDATE,NUll,NUll,NUll,'NewTest_CaseEvent_Trigger',SYSDATE,NUll,NUll);

          INSERT INTO  CASEEVENT
       (CASEEVENTID, CASEID, EventDate, EventComments, MethodOfCommunication, CreatedBy, CreationDate, LastUpdateBy, LastUpdateDate)
           VALUES
           (CASEEVENTID_PK.NextVal, CASEID_PK.CurrVal, :new.Date, 'New Test Result Available: include here the test result',
               Null, 'NewTest_CaseEvent_Trigger',SYSDATE,Null,Null);
     END IF;
END;

Answer 1

The fact that you got an error is less than useful. How are we supposed to guess which one?

Anyway, I tried to review code you wrote; here's what is obviously wrong:

• you declared result char(3) - beware of char datatype as it right-pads value with spaces up to total column length. Safer option is to use VARCHAR2. But, if those results really are strings whose length is always 3 characters, then OK - use it.
• not result = :new.result, but result := :new.result (missing colon)
• IF :new.PUIID IS IN (SELECT PUIID FROM CASE) THEN - two errors here:
  • you can't use subquery in this context. First find out whether :new.puiid exists in the case table (which means that you'll have to declare additional local variable), then use it in IF
  • even if you could use the subquery, it is not if :new.puiid IS in but if :new.puiid in (without "is")
• it is not elseif but elsif
• insert into case is wrong. You named 11 columns to be inserted, but provided 10 values. Fix that.
• insert into caseevent: you are inserting :new.date. date is invalid column name as it is reserved for the datatype name. That seems to be generally wrong, unless you used double quotes when naming the column, and that's again generally wrong as you should avoid double quotes in Oracle while creating objects

---

As I don't have your tables, I can't test it. However, here's code which might work (if you fix that "10 values into 11 columns" issue.

create or replace trigger newtest_caseevent_trigger 
  after insert on testresults
  for each row 
declare
  new_caseid  integer;
  result      char(3);           -- beware of CHAR!
  l_puuid     case.puiid%type;   -- newly declared variable
begin 
  result := :new.result;         --  add ":"

  -- subquery can't be used in IF, so you'll have to find whether :new.puiid
  -- exists in that table separately
  select max(puiid) into l_puiid from case where puiid = :new.puiid;

  if l_puiid is not null then
     select caseid 
       into new_caseid 
       from case
       where puiid = :new.puiid;

     insert into caseevent
       (caseeventid, caseid, eventdate, 
        eventcomments, 
        methodofcommunication, createdby, creationdate, 
        lastupdateby, lastupdatedate)
     values
       (caseeventid_pk.nextval, new_caseid, :new.date, 
        'New Test Result Available: include here the test result',
        null, 'NewTest_CaseEvent_Trigger',sysdate,
        null,null);

   elsif result = 'PST' then    -- "elsif", not "elseif"
      -- you are inserting 10 values into 11 columns; that won't work
      insert into case
        (caseid, puiid, activationdate, closingdate, 
         closingreason, exposuretypeid, caseworkerid, createdby,
         creationdate, lastupdateby, lastupdatedate)
      values
        (caseid_pk.nextval, :new.puiid, sysdate, null,
         null, null, 'NewTest_CaseEvent_Trigger', sysdate,
         null, null);

      insert into caseevent
        (caseeventid, caseid, eventdate, 
         eventcomments, 
         methodofcommunication, createdby, creationdate, lastupdateby, lastupdatedate)
      values
        (caseeventid_pk.nextval, caseid_pk.currval, :new.date,
         'New Test Result Available: include here the test result',
         null, 'NewTest_CaseEvent_Trigger', sysdate, null, null); 
  end if;
end;
/