CODEWITHSUNDEEP
oracle-databaseplsql
Lily
Lily

Reputation: 19

Oracle Trigger - ERROR: ORA-00903: invalid table name

I have a problem with my sql code.

CREATE OR REPLACE TRIGGER
BEFORE UPDATE ON TABAPPOGGIO
FOR EACH ROW
DECLARE
ERRORE EXCEPTION;
begin
IF :NEW_ConteggioPrenotazioni>150
THEN RAISE ERRORE;
END IF;
exception
when errore then raise_application_error (-20002, 'Sala Piena');
end;

I created a query before, for tabappoggio:

CREATE VIEW TABAPPOGGIO
AS
SELECT COUNT(Codice) AS ConteggioPrenotazioni
FROM PRENOTAZIONI
WHERE Sala='101'
GROUP BY Codice

But it keeps telling me ORA-00903: invalid table name. Any help, please, or suggestions? Thanks!

EDIT: SORRY!!! I accidentally deleted a comment of someone who told me I forgot the trigger name. I saw the tick mark and clicked it but... I failed. Sorry, I still need to realize how buttons work, I am pretty new here. ANYWAY THANK YOU!!! I literally forgot the trigger name as a fool. I didn't realize, all in panic. Thank you so much!!!

Upvotes: 1

Views: 1434

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

It seems you are thinking too complicated. You want to restrict records in the table PRENOTAZIONI, so write an insert trigger for that table.

CREATE OR REPLACE TRIGGER trg_too_many_prenotazioni
AFTER INSERT ON prenotazioni
DECLARE
  v_count INTEGER;
BEGIN
  select count(*) into v_count from prenotazioni where sala = 101;
  IF v_count > 150 THEN
    RAISE_APPLICATION_ERROR(-20002, 'sala piena');
  END IF;
END trg_too_many_prenotazioni;

(And maybe you want a SALA table with a record per sala and a column for the number of allowed prenotazioni rather then hard-coding 150 for sala = 101 here.)

Upvotes: 0

XING
XING

Reputation: 9886

You need INSTEAD OF trigger if you want to create a trigger on view. INSTEAD OF triggers are valid for DML events on views. They are not valid for DDL or database events.

Demo

CREATE TABLE BaseTable
  (ID     int PRIMARY KEY IDENTITY(1,1),
   Color          nvarchar(10) NOT NULL,
   Material       nvarchar(10) NOT NULL,
   ComputedCol AS (Color + Material)
  );


--Create a view that contains all columns from the base table.
CREATE VIEW InsteadView
AS SELECT ID, Color, Material, ComputedCol
FROM BaseTable;


--Create an INSTEAD OF INSERT trigger on the view.
CREATE TRIGGER InsteadTrigger on InsteadView
INSTEAD OF INSERT
AS
BEGIN

  INSERT INTO BaseTable
       SELECT Color, Material
       FROM inserted
END;

In your case it becomes:

CREATE OR REPLACE TRIGGER InsteadTrigger on  TABAPPOGGIO
INSTEAD OF UPDATE OR INSERT
DECLARE
ERRORE EXCEPTION;
begin
IF :NEW.ConteggioPrenotazioni>150
THEN RAISE ERRORE;
END IF;
exception
when errore then raise_application_error (-20002, 'Sala Piena');
end;

Note: You can read both the :OLD and the :NEW value, but you cannot write either the :OLD or the :NEW value.

Upvotes: 2

Aleksej
Aleksej

Reputation: 22969

You code has some syntax issues, but the main issue is that, if I understand your need, it will not do what you expect.

If I correctly interpret you need, you want to prevent inserting more than 150 rows in a table with a given value of sala. If so you can simply build a trigger on the table to check, performing the count after having inserted one or more rows.

For example:

create table PRENOTAZIONI( sala varchar2(10), codice number);

create or replace trigger checkPrenotazioni
after insert on prenotazioni
declare
    vNum   number;
    ERRORE EXCEPTION;
begin
    select count(codice)
    into vNum
    from prenotazioni
    where sala = '101';
    --
    IF vNum >150
        THEN RAISE ERRORE;
    END IF;
exception
    when errore
        then raise_application_error (-20002, 'Sala Piena');    
end;

Now I insert 150 rows with sala = '101' and everything goes well:

SQL> insert into prenotazioni(sala, codice) select '101', 101 from dual connect by level <= 150;

150 rows created.

If I try to insert the 151th row, the row is not inserted and I have:

SQL> insert into prenotazioni(sala, codice) values ('101', 101);
insert into prenotazioni(sala, codice) values ('101', 101)
            *
ERROR at line 1:
ORA-20002: Sala Piena
ORA-06512: at "ALEK.CHECKPRENOTAZIONI", line 15
ORA-04088: error during execution of trigger 'ALEK.CHECKPRENOTAZIONI'

SQL> select count(1) from prenotazioni;

  COUNT(1)
----------
       150

SQL>

Same thing if I try to insert 151 rows in one shot:

SQL> truncate table prenotazioni;

Table truncated.

SQL> select count(1) from prenotazioni;

  COUNT(1)
----------
         0

SQL> insert into prenotazioni(sala, codice) select '101', 101 from dual connect by level <= 151;
insert into prenotazioni(sala, codice) select '101', 101 from dual connect by level <= 151
            *
ERROR at line 1:
ORA-20002: Sala Piena
ORA-06512: at "ALEK.CHECKPRENOTAZIONI", line 15
ORA-04088: error during execution of trigger 'ALEK.CHECKPRENOTAZIONI'

Upvotes: 1

Related Questions