CODEWITHSUNDEEP
databaseoracle-databaseplsqloracle11g
Random guy
Random guy

Reputation: 923

problem in instead of delete views in oracle

I have a view as:

select * from ot.vw_rebellionrid;

which is showing me:

enter image description here

full_name is column of trainer table subject_name is column of subject table

The view has the query:

SELECT full_name, subject_name
     FROM ot.trainer, ot.subject;

So,I am learning the instead of delete tigger,so the code for instead of trigger is:

CREATE OR REPLACE TRIGGER ot.io_delete
INSTEAD OF DELETE ON ot.vw_RebellionRid
FOR EACH ROW
BEGIN
  DELETE FROM ot.trainer WHERE FULL_NAME = :old.FULL_NAME;
  DELETE FROM ot.subject WHERE SUBJECT_NAME= :old.SUBJECT_NAME;
END;
/

So,to execute this trigger,I execute the following statement:

delete from ot.vw_RebellionRid where subject_name='java';

when it is executed,then :

2 rows deleted.

According to my understanding,Since the two rows has subject_name as java the remaining output,I think will be after the deletion:

--------------------------
full_name | subject_ name |
---------------------------
Manish Sharma Oracle      |
ashwin        Oracle      |
---------------------------

But when I executed 

select * from ot.vw_RebellionRid ;

then,I got the output as EMPTY TABLE;

enter image description here

Since,there were 4rows,2rows were deleted,but when i again select the view then how I am getting empty table?I should have got remaining two rows.

Upvotes: 1

Views: 686

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

The problem is that when debugging your problem you didn't follow through. Your INSTEAD OF DELETE trigger certainly deleted SOMETHING, but to determine what was deleted you need to look at the tables underlying the view. If you perform your DELETE statement and then examine the contents of TRAINER and SUBJECT you'll find that the only thing left is the Oracle row in SUBJECT. And if you look at your trigger and think about what you told it to do you'll realize it did exactly what you told it to do, even if that might not have been what you wanted.

dbfiddle here

Upvotes: 2

Popeye
Popeye

Reputation: 35900

I think your trigger is working perfectly.

DELETE FROM ot.trainer WHERE FULL_NAME = :old.FULL_NAME;

It will delete 2 rows having name manish and ashwin as both are tagged with java.

The same way,

DELETE FROM ot.subject WHERE SUBJECT_NAME= :old.SUBJECT_NAME;

It will delete subject java from table subject so now table subject will have 1 record remaining i.e.Oracle

Now, your view query will join between 2 tables (trainer table with 0 rows and subject table with 1 rows) so it is producing no result.

You need to understand that,

delete from ot.vw_RebellionRid where subject_name='java';

is deleting total 3 records.

Cheers!!

Upvotes: 3

Related Questions