Re: Trigger compilation problem

On Thu, 07 Nov 2002 21:13:54 GMT, "gamaz" <gamaz_at_eathlink.net> wrote:

>Hi, I have written an after insert update trigger. However, while running it
>is giving error.
>I am new to trigger. Could someone give me advise how to handle the errors
>here. Thanks
>in advance. Regards.
>
>
>SQL> CREATE TRIGGER emp_a_iurow
> after insert or update of employee_id, last_name, first_name,
>middle_initial,
> dept_id, title, supervisor_id on employee
> for each row
> begin
> if inserting then
> insert into employee_log(employee_id, mod_user, mod_timestamp)
> values(:new.employee_id, username, timestamp);
> else
> insert into employee_log
> values(:old.employee_id, :old.last_name, :old.first_name,
>:old.middle_initial,
> :old.title, old,supervisor_id, username, timestamp);
> end if;
> end;
>
> /
>
>Warning: Trigger created with compilation errors.
>
>SQL> show error;
>Errors for TRIGGER EMP_A_IUROW:
>
>LINE/COL ERROR
>-------- -----------------------------------------------------------------
>3/1 PL/SQL: SQL Statement ignored
>4/26 PLS-00201: identifier 'USERNAME' must be declared
>6/1 PL/SQL: SQL Statement ignored
>7/93 PLS-00201: identifier 'OLD' must be declared
>SQL>
>

1. the pseudo function to retrieve the username is user not username.
2. :old is not available in after update triggers. You need to use a before update trigger

A little research in the documentation would have provided identical results.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Thu Nov 07 2002 - 15:25:12 CST