ORACLE trigger instead of insert on view with a nested table

Question

I have to write a trigger. It compiles everything, but if I want to insert something in my view, i get an error message. Maybe you can help me.

SET DEFINE off;
CREATE OR REPLACE TRIGGER LieferantOV_trig

INSTEAD OF INSERT
ON LIEFERANT_OV
FOR EACH ROW
BEGIN 
  IF INSERTING THEN
  INSERT INTO Lieferant (LiefNr, Name, Adresse)  
  VALUES(:new.LiefNr, :new.Name, ntTAdresse());

  INSERT INTO TABLE (SELECT Adresse FROM Lieferant  ) VALUES
  (TAdresse(:new.Straße, :new.PLZ, :new.Ort));

  END IF;
  END;

INSERT INTO Lieferant_OV 
VALUES(752443, 'Laepple Teublitz', 'Maxstr. 12', '93158', 'Teublitz');

For the nested Table

CREATE OR REPLACE TYPE TAdresse AS OBJECT(
Straße VARCHAR2(50),
PLZ VARCHAR2(5),
Ort VARCHAR2(50)
);



CREATE TABLE Lieferant(
LiefNr number(6) PRIMARY KEY,
Name varchar2(20) NOT NULL
);
1.
CREATE OR REPLACE TYPE ntTAdresse AS TABLE OF TAdresse;
2.
ALTER TABLE Lieferant ADD Adresse ntTAdresse NESTED TABLE Adresse STORE AS TAdresseNT;


CREATE OR REPLACE VIEW Lieferant_OV (LiefNr, Name, Straße, PLZ, ORT) 
AS SELECT k.LiefNr, k.Name, l.Straße, l.PLZ, l.Ort 
FROM Lieferant k, table(k.Adresse) l;

Hey, i added a bit more code. Got this error: SQL-ERROR: ORA-04098: Trigger 'S74040.LIEFERANT_OV_TRIG' ist ungültig und konnte nicht neu bestätigt werden 04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation" — Nerdluuuus
– Nerdluuuus, Commented Jan 11, 2017 at 12:39
what if you already had row with 752443 LeifNr? you want to update the row? — Gurwinder Singh
– Gurwinder Singh, Commented Jan 11, 2017 at 13:01

Wernfried Domscheit · Accepted Answer · 2017-01-11 13:18:45Z

The syntax should be like this:

CREATE OR REPLACE TRIGGER LieferantOV_trig
   INSTEAD OF INSERT
   ON LIEFERANT_OV
   FOR EACH ROW
BEGIN 
   INSERT INTO Lieferant (LiefNr, Name, Adresse)  
   VALUES(:new.LiefNr, :new.Name, ntTAdresse(TAdresse(:new.Straße,:new.PLZ,:new.ORT));

END;

You can skip IF INSERTING THEN because your trigger fires only on INSERT

Note, by this each record have at maximum only one address, thus a nested table does not make much sense.

In order to add an address to existing Lieferant you can do this one:

CREATE OR REPLACE TRIGGER LieferantOV_trig
   INSTEAD OF INSERT
   ON LIEFERANT_OV
   FOR EACH ROW

DECLARE
   lieferantCount INTEGER;
BEGIN 

   select count(*) 
   into lieferantCount 
   from Lieferant 
   where LiefNr = :new.LiefNr
       and Name = :new.Name;

   if lieferantCount = 0 then
      INSERT INTO Lieferant (LiefNr, Name, Adresse)  
      VALUES(:new.LiefNr, :new.Name, ntTAdresse(TAdresse(:new.Straße,:new.PLZ,:new.ORT)));
   else
      UPDATE Lieferant 
      SET Adresse = Adresse MULTISET UNION ntTAdresse(TAdresse(:new.Straße,:new.PLZ,:new.ORT))
      WHERE LiefNr = :new.LiefNr
         and Name = :new.Name;
   end if;

END;

Gurwinder Singh · Accepted Answer · 2017-01-11 13:17:39Z

0

I think you want do this if you want to keep one row per insertion.

CREATE OR REPLACE TRIGGER LieferantOV_trig

INSTEAD OF INSERT
ON LIEFERANT_OV
FOR EACH ROW
BEGIN
  INSERT INTO Lieferant (LiefNr, Name, Adresse)  
  VALUES(:new.LiefNr, :new.Name, ntTAdresse(TAdresse(:new.Straße, :new.PLZ, :new.Ort)));
END;
/

This works if the Adresse column contain only one Adress. But that is not purpose of a nested table. So you probably want to check if there is an existing row in the table with given LiefNr. If yes, only insert into the nested table.

edited Jan 11, 2017 at 13:17

answered Jan 11, 2017 at 12:51

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

1 Comment

Nerdluuuus Over a year ago

Okay thanks. No this trigger compiles with no errors as well in this form. I had this nearly the same. But I get an error as well if I insert a row in my view like this: INSERT INTO Lieferant_OV VALUES(752443, 'Laepple Teublitz', 'Maxstr. 12', '93158', 'Teublitz');

Collectives™ on Stack Overflow

ORACLE trigger instead of insert on view with a nested table

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related