Error on a script Trigger...

[omacron]

I am trying to create a trigger but getting the following error;

Warning: execution completed with warning
anonymous block completed

Here is my script;

CREATE OR REPLACE TRIGGER XXX.SALES_UPDATE_DETAIL
AFTER UPDATE
OF AMOUNTAPPLIED
  ,FULLYAPPLIED
ON XXX.SALES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  UPDATE XXX.DETAIL SET 
  AmountApplied = :NEW.AmountApplied,
  FullyApplied = :NEW.FullyApplied,
  Balance = :OLD.Amount - :NEW.AmountApplied
  WHERE Receipt = :OLD.Receipt AND linenum = :OLD.linenum;
END;

Sorry I am new to Oracle and not sure what is wrong.

 

[jaxtell]

Assuming you're using sqlplus to run this, type show errors. Paste the errors here so we can get a better idea of the problem. A warning isn't necessarily an error. It might just mean there is room for improvement.

 

[omacron]

I am using SQL Developer-1.2.1.3213. I will try to get the file to run in sqlplus

 

[omacron]

After figuring out how to run the script in sqlplus (sorry as i said very new to Oracle) it returns an equally unhelpfully same error message:

Warning: Trigger created with compilation errors.

 

[jaxtell]

When I use the same version of sql developer, and compile a package that I know to have errors, the messages tab says MY_PKG Body Compiled (with errors). Then I click on the compiler tab, and I get a list of the actual errors.
Error(1020,13): PL/SQL: SQL Statement ignored
Error(1021,26): PL/SQL: ORA-00942: table or view does not exist

This works the same when I try to compile an invalid trigger. List the errors listed in the compiler tab, and hopefully I can help.

If you use sqlplus:
run the script to create the trigger.
type show errors

SQL> CREATE OR REPLACE TRIGGER my_trigger
  2  ----------------------------------------------------------------
----------------------------------------------------------------
  8     AFTER INSERT
  9     ON my_table
 10     REFERENCING NEW AS NEW OLD AS OLD
 11     FOR EACH ROW
 12  WHEN (
 ...
 14        )
 15  DECLARE
...
 18  BEGIN
 ...
 25  EXCEPTION
 ..
 46  END;
 47  /

Warning: Trigger created with compilation errors.

SQL> show errors
Errors for TRIGGER my_trigger:

LINE/COL ERROR
-------- -----------------------------------------------------------------
5/4      PL/SQL: Statement ignored
5/4      PLS-00306: wrong number or types of arguments in call to
         '************'

SQL>

 

[omacron]

Thanks for the info here is the error message;

LINE/COL ERROR
-------- -----------------------------------------------------------------
8/4      PLS-00103: Encountered the symbol "INSERT"

 

[jaxtell]

Are you running another command after you try to create the trigger? I think the line count starts at the declare (or begin if there is no declare). If thats true, your trigger only has 7 lines, but the error says its on the 8th line. Try adding a / at the end. Then run any additional commands after that.

CREATE OR REPLACE TRIGGER XXX.SALES_UPDATE_DETAIL
AFTER UPDATE
OF AMOUNTAPPLIED
  ,FULLYAPPLIED
ON XXX.SALES
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
  UPDATE XXX.DETAIL SET
  AmountApplied = :NEW.AmountApplied,
  FullyApplied = :NEW.FullyApplied,
  Balance = :OLD.Amount - :NEW.AmountApplied
  WHERE Receipt = :OLD.Receipt AND linenum = :OLD.linenum;
END;
/

Let me know if that works for you.

 

[omacron]

thank you it was the slash. I learned how to script in MS SQL so this slash thing is very new to me, kind of feel bad.