Please help on Triggers! Schema is given above. Consider the following relationa

Question

Please help on Triggers! Schema is given above.

Consider the following relational schema and corresponding sample data maker) Product model, type, Distributor (model name, price (model, speed, ram, hd) PC Laptop model, speed, ram, hd, screen (model, color, type) Printer The keys are underlined. Distributor model, PC model, Laptop. model, and Printer. model are all foreign keys to Product. model. Referential integrity constraints are enforced through CASCADE for DELETE and SET NULL for UPDATE, respectively. model speed had Tam, 100001 3.2 32 4096 100002 2.1 16 1024 model maker model type name price 100003 3.2 32 8192 P1 100001 D3 100001 pc 680 100004 2.7 64 8192 100002 pc P2 100002 D3 630 100005 2.5 32 4096 100003 pc P2 100004 D3 780 100006 2.2 16 4096 100004 pc P3 100005 D3 550 100005 pc P1 2000003 D1 1000 model speed ram had 100006 pc P2 200003 D2 800 Screen 200001 laptop P4 20000003 D3 980 3.2 200001 165 4096 15 200002 laptop P5 20000004 P3 680 200002 2.1 16 1024 13 200003 laptop P1 200008 P3 880 200003 3.2 32 512 17 2000004 laptop P3 20000009 P3 620 200004 2.7 256 15 2000005 laptop P3 200000010 D1 540 200005 2.5 8 4096 15 2000006 laptop P2 200000010 D2 570 200006 2.2 16 4096 17 2000007 laptop P4 2000007 P3 930 200007 2.2 8 2048 17 200008 200008 laptop P1 200000007 P2 890 3.2 32 4096 13 2000009 laptop P1 20000004 D3 680 2000009 2.8 8 2048 15 200010 laptop P1 200004 P1 580 2000010 3.2 32 512 13 300001 printer P6 200000004 P2 640 300002 printer P6 30000004 D3 180 model color type 3000003 printer P2 300000002 D1 280 300001 T laser 3000004 printer P3 3000002 D3 200 300002 T laser 300005 printer P3 3000001 P3 80 3000003 F ink-jet 3000004 F laser 300005 F laser

Explanation / Answer

//Following is trigger code for first subquestion.

CREATE TRIGGER trigger1

BEFORE

INSERT ON Distributor

DECLARE

total_sum NUMBER;

BEGIN

SELECT sum(price) into total_sum where name=NEW.name;

IF ((total_sum+new.price)>5000)

THEN

INSERT INTO Distributor VALUES(new.model,new.name,5000-total_sum-1);

ELSE

INSERT INTO Distributor VALUES(NEW.model,NEW.name,NEW.price);

END IF;

END;

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.