Re: triggers on views

On Dec 1, 10:28 pm, Shakespeare <what..._at_xs4all.nl> wrote:
> Veganders schreef:
>
>
>
> > Creating a database as a part of a school project, I ran into a
> > problem that was solved by switching place of two rows that by the
> > look of them would suggest their order would be irrelevant.
> > The situation was roughly as follows:
> > A view was created as a union of two tables.
> > Two "instead of" trigger was created to handle insert and deletes on
> > the view
> > In the delete trigger, if the row to be deleted is in the first of the
> > two tables, a row from the second table is deleted and then inserted
> > into the first one. It was the two lines to move the row from the
> > second table to the first that caused to problem. In one of the cases,
> > the row was just inserted into the first table, but never removed from
> > the second.
> > Trying to find a reason for this, the only thing I came up with, that
> > could explain this odd behaviour, is that a trigger on a view will be
> > executed not only on insertion or deletion on the view itself, but
> > also on its underlying tables. Is this a correct assumption or is my
> > error someplace else?
>
> > Best regards
> > Anders
>
> Anders,
>
> I don't think your assumption is correct, but if you post your code, we
> can always check...
>
> Shakespeare

Here's the code, but I've removed as much as possible to focus on the problem. The original code handles registrations for courses at a fictive school, but this example only handles one queue for simplicity.

CREATE TABLE Queued (

	ID INT,
        queueposition INT NOT NULL,
        PRIMARY KEY (student, course),
	UNIQUE (queueposition),

CREATE TABLE Registered (

        ID INT,
        PRIMARY KEY (student, course),

CREATE VIEW RegQueView AS
SELECT * FROM
((SELECT student, 'registered' FROM Registered) UNION (SELECT student, 'queued' FROM Queued));

CREATE OR REPLACE TRIGGER Registration
INSTEAD OF INSERT ON RegisteredWaitingVIEW REFERENCING NEW AS new
FOR EACH ROW
DECLARE
currentNum INT;
BEGIN
  SELECT COUNT (*) INTO currentNum FROM Registered;   IF currentNum < 5 THEN
    INSERT INTO Registered VALUES(:new.ID);   ELSE
    INSERT INTO Queued VALUES(:new.ID, seq_queued.nextval); -- a sequence to get the next queue position
  END IF;
END;
/

CREATE OR REPLACE TRIGGER Unregistration INSTEAD OF DELETE ON RegisteredWaitingVIEW REFERENCING OLD AS old
FOR EACH ROW
DECLARE
firstStudent Registered.ID%TYPE;
waitingNum INT;
isRegistered INT;
BEGIN
  SELECT COUNT(*) INTO isRegistered FROM Registered WHERE ID = :old.ID;
  IF isRegistered > 0 THEN
    DELETE FROM Registered WHERE ID = :old.ID;     SELECT COUNT(*) INTO waitingNum FROM Queued;     IF waitingNum > 0 THEN

      SELECT student INTO firstStudent
      FROM Queued WHERE queueposition = (SELECT MIN(queueposition)
FROM Queued);
      DELETE FROM Queued WHERE ID = firstStudent; -- Here are the
lines that changes the behaviour
      INSERT INTO Registered VALUES (firstStudent);   -- depending on

    END IF;
  ELSE
    DELETE FROM Queued WHERE ID = :old.ID;   END IF;
END;
/

The lines discussed previously is commented in the code. The problem arises when there are people in the queue, and someone registered is deleted. When this happens the first one in the queue should be registered instead, but when the insert line was before the delete line, the person was never removed from the queue.

/Anders Received on Tue Dec 02 2008 - 02:22:23 CST