Home » Other » Client Tools » Error ORA-04076: on trigger
Error ORA-04076: on trigger [message #38658] Sat, 04 May 2002 13:17 Go to next message
Sid
Messages: 38
Registered: May 1999
Member
I am creating this trigger on employer table to print to the user that employer WARD's commission has increased. But gives me error during compilation.

SET SERVEROUTPUT ON
CREATE OR REPLACE TRIGGER verify_wards_comm
AFTER UPDATE OF comm ON empy
FOR EACH ROW
WHEN (ename = 'WARD' )
BEGIN
DBMS_OUTPUT.PUT_LINE('WARD commission has increased by' + ( (:old.comm - :new.comm)/ :old.comm ) * 100 );
END;
/

CREATE OR REPLACE TRIGGER verify_wards_comm
*
ERROR at line 1:
ORA-04076: invalid NEW or OLD specification
Re: Error ORA-04076: on trigger [message #38659 is a reply to message #38658] Sat, 04 May 2002 15:02 Go to previous messageGo to next message
Pinakin
Messages: 3
Registered: December 2000
Junior Member
DBMS_OUTPUT.PUT_LINE('WARD commission has increased by' || ( (:old.comm - :new.comm)/ :old.comm ) * 100 );

Should work.
Re: Error ORA-04076: on trigger [message #38661 is a reply to message #38658] Sat, 04 May 2002 16:25 Go to previous messageGo to next message
Sid
Messages: 38
Registered: May 1999
Member
Have tried what Pinakin has suggested but still get same error:

SQL> SET SERVEROUTPUT ON
SQL> CREATE OR REPLACE TRIGGER verify_wards_comm
2 AFTER UPDATE OF comm ON empy
3 FOR EACH ROW
4 WHEN (ename = 'WARD' )
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('WARD commission has increased by' || ( (:old.comm - :new.comm)/ :old.comm
) * 100 );
7 END;
8 /
CREATE OR REPLACE TRIGGER verify_wards_comm
*
ERROR at line 1:
ORA-04076: invalid NEW or OLD specification
Re: Error ORA-04076: on trigger [message #38663 is a reply to message #38658] Sat, 04 May 2002 21:58 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
In addition to the change already mentioned (use || for string concatenation instead of +), you need to change the WHEN condition to:

WHEN (new.ename = 'WARD')


The WHEN condition must reference a new or old value, but it doesn't use the colon prefix as done in the PL/SQL block section.
Re: Error ORA-04076: on trigger [message #38669 is a reply to message #38658] Sun, 05 May 2002 11:03 Go to previous message
Pinakin
Messages: 3
Registered: December 2000
Junior Member
DBMS_OUTPUT.PUT_LINE('WARD commission has increased by' || ( (:old.comm - :new.comm)/ :old.comm ) * 100 );

Should work.
Previous Topic: trigger, update mire
Next Topic: Re: Problem with SQL Trace Command
Goto Forum:
  


Current Time: Thu Mar 28 17:38:14 CDT 2024