StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODeleting rows on a complex view which is used by a trigger
text
Body
copied!<p>I get this error when trying to delete a row on a view</p> <p>"<strong>ORA-01732: data manipulation operation not legal on this view</strong> "</p> <p>SQL Command issued is</p> <pre><code>DELETE FROM RegisterHelper WHERE StudentID = 111111111 AND CourseID = 'ASD123'; </code></pre> <p>This creates the errors. I've doublechecked that these values exists.</p> <p>My view is</p> <pre><code>CREATE VIEW RegisterHelper AS SELECT studentId, courseID, NULL as position, 'registered' AS status FROM registeredOn UNION SELECT studentId, courseID, position, 'waiting' AS status FROM waitingOn; </code></pre> <p>This view creates a complete list of waiting and registered students on all courses, together with their status (which is used in the trigger). All the data given here is essential to the trigger.</p> <p>Trigger is</p> <pre><code>CREATE OR REPLACE TRIGGER CourseUnregistration INSTEAD OF DELETE ON RegisterHelper etc </code></pre> <p>Tables are as follows</p> <pre><code>CREATE TABLE RegisteredOn ( StudentID REFERENCES Student(StudentID) NOT NULL, CourseID REFERENCES Course(CourseID) NOT NULL ); CREATE TABLE WaitingOn ( StudentID REFERENCES Student(StudentID) NOT NULL, CourseID REFERENCES Course(CourseID) NOT NULL, Position INT NOT NULL, PRIMARY KEY(StudentID, CourseID) ); </code></pre> <p>Since I only delete from tables with references values, there will be no problems with deleting values with are referenced (obviously) - this should pose no problem.</p> <p>An interesting thing was that it actually worked to issue the DELETE command on the RegisterHelper view a few hours ago. For some reason it has stopped working, and I can't remember changing it. </p> <p>The reason the trigger exists on the RegisterHelper view is because students are both inserted and removed on the RegisteredOn and WaitingOn table. Inserts works fine, removal does not, as I get the error.</p> <p>Simply put, I just want the trigger to work when something is deleted on the RegisteredOn or WaitingOn tables. If anyone has a different solution I'm open to it.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload