Re: triggers on views

On Dec 2, 9:22 am, Veganders <vegand..._at_gmail.com> wrote:
> 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
> the order of them
>     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

A small error have worked its way into the code while simplifying it. Where the view says student, it should instead be ID, and also in the two tables, the primary keys should be ID instead of student, and no course.

/Anders Received on Tue Dec 02 2008 - 07:28:05 CST