Suppose we create table R using: CREATE TABLE R(A NUMBER, B NUMBER) Now we add t
ID: 3582322 • Letter: S
Question
Suppose we create table R using:
CREATE TABLE R(A NUMBER, B NUMBER)
Now we add the following trigger:
CREATE TRIGGER trig
AFTER INSERT OF R
REFERENCING NEW ROW AS new
FOR EACH ROW
INSERT INTO R(A,B)
(SELECT DISTINCT R.A as A, new.B as B
FROM R, new
WHERE new.A = R.B)
EXCEPT
(SELECT DISTINCT A,B FROM R)
Suppose we now insert tuples (1,2), (2,3) and (3,4) into R, in that order. After some of these inserts, trigger trig may add further tuples into R. After all the inserts are done, which tuples are in relation R?
Explanation / Answer
(1,2) ---- will not insert any new tuple .
(2,3) ---- as b(2) already there matches with new a(2) , so it will trigger the insertion of (1,3)
(1,3)
(3,4) ----- triggers the insertion of (1,4) and (2,4)
(1,4)
(2,4) ---(1,4) already there so according to EXCEPT it should not be inserted as already there.
so tuples in Relation R are:
(1,2),(2,3),(1,3),(3,4),(1,4) and (2,4)