Re: Trigger not compiling

From: Michael Gast <mig-sm_at_web.de>
Date: Fri, 08 Nov 2002 10:05:44 +0100
Message-ID: <3DCB7E68.3070303@web.de>

Hi Gamaz,

gamaz schrieb:
> Hi, I am trying to create a trigger. However, it gives me the following
> error message. I could not figure out why the error message is coming. Any
> help is appreciated in advance. Thanks.
>
> SQL> create trigger emp_a_iurow
> 2 after insert or update of empno, ename, job, mgr, hiredate, sal,
> comm, deptno on
> 3 emp
> 4 for each row
> 5 begin
> 6 if inserting then
> 7 insert into emp_log(emp_no, mod_user, mod_timestamp)
> 8 values(:new.empno, user, timestamp);
> 9 else
> 10 insert into employee_log
> 11 values(:old.empno, :old.ename, :old.job, :old.mgr, :old.mgr,
> :old.hiredate, old.comm,
> 12 user, timestamp);
> 13 end if;
> 14 /
>
> Warning: Trigger created with compilation errors.
>
> SQL> l
> 1 create trigger emp_a_iurow
> 2 after insert or update of empno, ename, job, mgr, hiredate, sal,
> comm, deptno on
> 3 emp
> 4 for each row
> 5 begin
> 6 if inserting then
> 7 insert into emp_log(emp_no, mod_user, mod_timestamp)
> 8 values(:new.empno, user, timestamp);
> 9 else
> 10 insert into employee_log
> 11 values(:old.empno, :old.ename, :old.job, :old.mgr, :old.mgr,
> :old.hiredate, old.comm,
> 12 user, timestamp);
> 13* end if;
> SQL> show error
> Errors for TRIGGER EMP_A_IUROW:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 10/0 PLS-00103: Encountered the symbol "end-of-file" when expecting
> one of the following:
> begin declare end exception exit for goto if loop mod null
> pragma raise return select update while <an identifier>
> <a double-quoted delimited-identifier> <a bind variable> <<
> close current delete fetch lock insert open rollback
> savepoint set sql execute commit forall
> <a single-quoted SQL string>

Read your error message carefully:
It says that a symbol is missing. If you format yor code using indents, it is obviously which symbol is missing (have a look on the end of yor code):

create trigger emp_a_iurow
  after insert or update of empno, ename, job, mgr, hiredate, sal,

                            comm, deptno

  on emp
for each row
begin
  if inserting
  then
    insert into emp_log(emp_no, mod_user, mod_timestamp)     values(:new.empno, user, timestamp);   else
    insert into employee_log

      values(:old.empno, :old.ename, :old.job, :old.mgr, :old.mgr,
             :old.hiredate, :old.comm, user, timestamp);

  end if;
END;
/

In addition you have forgotten a ':' in your insert statement for ':old.comm'.

-- 
All emails sent to this address are never read and never will be
answered. Sorry, but until someone cleans up the spam mess, that's the
way it has to be.

E-Mails, die direkt an diese Adresse geschickt werden, lese und
beantworte ich nicht. Ich bedauere diesen Umstand sehr, kenne derzeit
aber keine bessere Möglichkeit, um die Spam-Flut abzustellen.

Mit freundlichen Grüßen / Best Regards
Michael Gast
SEPP MED GmbH

Received on Fri Nov 08 2002 - 03:05:44 CST