CODEWITHSUNDEEP
sqloracle-databaseplsqldatabase-trigger
Darius
Darius

Reputation: 15

Error, trigger instead of is invalid and failed re-validation

I try to create an instead of trigger. Its purpose it's to insert in 3 tables instead of a view that i have, but I have a series of problems. 

Create or replace trigger trigg_view
Instead of Insert ON Carti_Beletristica
for each row

Begin

dbms_output.put_line('i dont know');

End;

This is the very basic code from which i want to start. It let me Create it, but when i try an insert in my view (Carti_Beletristica) i get the next error

ORA-04098: trigger 'RO_A372_SQL_S20.INSERT_VIEW_TRIGG' is invalid and failed re-validation

This is very frustrating because it is a very simple trigger and I can't continue... After that, how I will generate de primary keys ? Because in the view i dont have something like that. My idea was to select the max from the primary keys in a table, then add one, then use this value, but i get a lot of errors.

CREATE VIEW Carti_Beletristica AS
SELECT titlu, nr_pagini, nr_exemplare, nume AS autor, telefon
FROM Carte NATURAL JOIN Autor JOIN Persoana ON (id_pers = id_aut)
WHERE upper(gen) = 'BELETRISTICA'

This is the view. 

Create table Persoana(
    id_pers number(10) not null,
    nume varchar2(100) not null,
    telefon varchar2(15) not null,

    Constraint persoana_id_pers_pk primary key(id_pers)
 );

Create table Carte(
    id_carte number(10) not null,
    titlu varchar2(100) not null,
    nr_pagini number(10) not null,
    nr_exemplare number(10) not null,
    gen varchar2(20) not null,

    Constraint carte_id_carte_pk primary key(id_carte)  
 );

Create table Autor(
    id_carte number(10) not null,
    id_aut number(10) not null,

    Constraint autor_pk primary key(id_carte,id_aut),

    Constraint autor_id_carte_fk foreign key(id_carte) references Carte(id_carte),
    Constraint autor_id_aut_fk foreign key(id_aut) references Persoana(id_pers)

);

Can you help me out a bit ? An insert on the view would look like that

Insert into Carti_Beletristica(titlu,nr_pagini,nr_exemplare,autor,telefon)
values('tiltu',69,96,'otor','07phonenumber')

EDIT:

This is what I tried for the primary key

Create or replace trigger trigg_view
Instead of Insert ON Carti_Beletristica
for each row
declare
    aux persoana.id_pers%type;
Begin

    select max(id_pers)+1 into aux from Persoana;

    dbms_output.put_line(aux);

End;

Upvotes: 0

Views: 73

Answers (1)

APC
APC

Reputation: 146239

My idea was to select the max from the primary keys in a table, then add one, then use this value,

You may think "my idea worked" but this is very bad practice:

select max(id_pers)+1 into aux from Persoana;

This is an inefficient way of getting a primary key identifier. More importantly it is unsafe because it won't work in multi-user environments: two users inserting into that table at the same time will derive the same "next value" (because of read-commit isolation) then one of those user will get a duplicate key violation when they commit their transaction.

The correct solution is to use Oracle's built-in unique key generators. Prior to 12c that meant a sequence. For your persoana table that would mean creating a sequence called persoana_seq that you would reference in your trigger as :

aux := persoana_seq.nextval;

Sequences are the most performative mechanism for generating a series of guaranteed unique numbers.

In Oracle 12c we can define columns as IDENTITY columns. This provides us with an auto-incrementing column :

create table persoana (
       id generated always as identity primary key,
       ....

persoana.id will be populated with a unique value automatically on insert, without any further action on our part. (Identity columns have associated sequences under the covers, it's just we don't need to worry about them.)

Upvotes: 1

Related Questions