Home » SQL & PL/SQL » SQL & PL/SQL » Trigger created with error (Oracle 9i win xp)
Trigger created with error [message #319090] Fri, 09 May 2008 00:43 Go to next message
aslamsadia
Messages: 19
Registered: December 2006
Junior Member
I have coded a trigger for updation of BALANCE column in BILL_MR table, but trigger created with compilation error. Code is :-

CREATE OR REPLACE TRIGGER UPD_BILL_AMT
AFTER UPDATE OF BILL_AMT ON BILL_MR
FOR EACH ROW
DECLARE
PLUS_DIFF NUMBER(12,2);
MINUS_DIFF NUMBER(12,2);
BEGIN
IF UPDATING THEN
IF :NEW.BILL_AMT > :OLD.BILL_AMT THEN
PLUS_DIFF := :NEW.BILL_AMT - :OLD.BILL_AMT;
UPDATE BILL_MR SET BALANCE = BALANCE + PLUS_DIFF;
END IF;
IF :NEW.BILL_AMT < :OLD.BILL_AMT THEN
MINUS_DIFF := :OLD.BILL_AMT - :NEW.BILL_AMT;
UPDATE BILL_MR SET BALANCE = BALANCE _ MINUS_DIFF;
END IF;
END IF;
END;
The error is :-
LINE/COL ERROR
-------- -------------------------------------
12/4 PL/SQL: SQL Statement ignored
12/41 PL/SQL: ORA-00911: invalid character

Pls help. Sad
Re: Trigger created with error [message #319093 is a reply to message #319090] Fri, 09 May 2008 00:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
2/ Which one is line 12. Use SQL*plus and copy and paste your session

3/ You can neither read nor write the table you are currently modifying: the UPDATE statement are wrong. Use :NEW.BALANCE := ...

Regards
Michel
Re: Trigger created with error [message #319138 is a reply to message #319090] Fri, 09 May 2008 02:32 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you not replace this whole piece of code with:
CREATE OR REPLACE TRIGGER UPD_BILL_AMT
AFTER UPDATE OF BILL_AMT ON BILL_MR
FOR EACH ROW
BEGIN
  :new.BALANCE := :new.BALANCE + :new.BILL_AMT - :old.BILL_AMT;
END;
Previous Topic: Merge problem
Next Topic: retrieve single record row
Goto Forum:
  


Current Time: Sun Dec 04 00:50:10 CST 2016

Total time taken to generate the page: 0.12646 seconds