0

when i run my code i keep getting an error saying that the statement is being ignored at line 2

CREATE OR REPLACE TRIGGER invalid_Year
  BEFORE INSERT OR UPDATE OF Financial_Year ON Area_Offence
  FOR EACH ROW
BEGIN
  IF :NEW.Financial_Year > (SELECT to_char(Max(Offence_Date), 'YYYY') FROM Offence) THEN
    dbms_output.put_line('Invalid Year entered');
    raise_application_error(-20001, 'Invalid Year entered');
  END IF;
END invalid_Year;
jim mcnamara
  • 16,005
  • 2
  • 34
  • 51
bev
  • 49
  • 6
  • Unrelated but: don't compare apples (numbers) to oranges (strings). If `Financial_Year` is number, then you should compare that to a number: `max(extract(year from offence_date))`. `to_char()` returns a string. –  Dec 19 '14 at 18:10
  • the statement is still ignored when i change the code to what u suggested – bev Dec 19 '14 at 18:14
  • possible duplicate of [if (select count(column) from table) > 0 then](http://stackoverflow.com/questions/10200281/if-select-countcolumn-from-table-0-then) – Jon Heller Dec 19 '14 at 20:55

1 Answers1

0
dbms_output.put_line('Invalid Year entered');

There is no tty or terminal for a trigger. The process context is in one of the oracle server processes not the user process. So, the complaint arises on this line.

I think this was okay in older versions of oracle up to oracle 9i.

Made a few changes. See below. I was asleep at the switch.

[correction] I looked in some old code, no triggers have dbms_output calls. I saw it in stored procedures. So, I would conclude it never was allowed

CREATE OR REPLACE TRIGGER invalid_Year
  BEFORE INSERT OR UPDATE OF Financial_Year ON Area_Offence
  FOR EACH ROW
DECLARE  
  offense_date varchar2(4):=NULL;
BEGIN
    SELECT to_char(Max(Offence_Date), 'YYYY' )
      into offense_date
    from Offence;  
  IF :NEW.Financial_Year > Offense_Date  THEN
    dbms_output.put_line('Invalid Year entered');
    raise_application_error(-20001, 'Invalid Year entered');
  END IF;
END invalid_Year;
jim mcnamara
  • 16,005
  • 2
  • 34
  • 51
  • I don't think a `dbms_output` call will trigger that error message. –  Dec 19 '14 at 18:10
  • i took the dbms_output.put_line('Invalid Year entered'); out of my statement and it still gives the statement ignored error – bev Dec 19 '14 at 18:11