Question

Please help and answer both parts

ASSIGNMENT 5: CREATE & EXECUTE Database Trigger titled 'stock update' that automatically inserts a row in stock log table, whenever an UPDATE is made in stock table. STOCK table stkcode stkfirm AR stkprice stkty stkdv stkpe natcode Abyssinian Ruby 33.82 22010 1.32 5 UK Bombay Duck Burmese Blephant 0.07 Bolivian Sheep Canadian Sugar52.78 4716 2.5020UK Fire Eye Freedonia Copper 27.50 10529 1.84 16 UK Georgia Peach Indian Lead &Zinc 37.75 6390 3.00 12 UK Indooroopilly Ruby 15.92 561470.50 20 AUS 25.55 167382 1.00 12 IND 12.75 231678 1.78 14UK 16.90 100 2.35 387333 0.20 5 54713 0.01 3 UK BS CS EYE FC 3.00 12 USA STOCK LOG table stkcode old stkprice new stkprice old stkqty new stkqty update stktime 92.00 33.82 12.75 53.87 33.82 52.78 25 22010 231678 816122 22010 2019-04-09 07:13:35 2019-04-09 09:54:24 2019-04-09 12:14:40 2019-04-09 12:44:19 2019-04-09 13:19:57 2019-04-09 13:23:43 90.00 31.82 12.75 53.87 33.82 52.78 AR 22010 231678 816122 22010 716 AR CS DELIVERABLE 5a: screen shot of the Trigger code DELIVERABLE 5b: screen shot of the stock log table for the following updates made in STOCK table: Update: For the row where stkcode -BD, update stkpe-15, Update 2: For the row where stkcode -BE, update stkpe-5 Update 3: For the row where stkcode FC, update stkpe 20

Answer 1

CREATE OR REPLACE TRIGGER stock_update

ON Stock

FOR UPDATE

AS

BEGIN

         INSERT INTO stock_log

         (stkcode, old_stkprice, new_stkprice, old_stkqty, new_stkqty, update_stktime)

         SELECT d.stkcode, d.stkprice, i.stkprice, d.stkqty, i.stkqty, CURRENT_TIMESTAMP

          FROM deleted d, inserted i

          where d.stkcode = i.stkcode

END

GO

In this trigger we are inserting values to stock_log table by fetching values from deleted and inserted stock table using

         SELECT d.stkcode, d.stkprice, i.stkprice, d.stkqty, i.stkqty, CURRENT_TIMESTAMP

          FROM deleted d, inserted i

          where d.stkcode = i.stkcode

(during update there will be two table maintained by the DMBS

1. is old or deleted

2. is new or updated or inserted )