OraFAQ Forum: Client Tools » Error ORA-04076: on trigger

Home » Other » Client Tools » Error ORA-04076: on trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error ORA-04076: on trigger [message #38658]

Sat, 04 May 2002 13:17

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

Report message to a moderator

Re: Error ORA-04076: on trigger [message #38659 is a reply to message #38658]

Sat, 04 May 2002 15:02

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.

Report message to a moderator

Re: Error ORA-04076: on trigger [message #38661 is a reply to message #38658]

Sat, 04 May 2002 16:25

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

Report message to a moderator

Re: Error ORA-04076: on trigger [message #38663 is a reply to message #38658]

Sat, 04 May 2002 21:58

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.

Report message to a moderator

Re: Error ORA-04076: on trigger [message #38669 is a reply to message #38658]

Sun, 05 May 2002 11:03

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.

Report message to a moderator

Previous Topic:	trigger, update mire
Next Topic:	Re: Problem with SQL Trace Command

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 18 11:31:39 CST 2025