Oracle SQL Write a Trigger which outputs an appropriate message (based on the DM

Question

Oracle SQL

Write a Trigger which outputs an appropriate message (based on the DML action performed) whenever an insert, update or delete is performed on the BOOKS table.

CREATE TABLE Books
(ISBN VARCHAR2(10),
Title VARCHAR2(30),
PubDate DATE,
PubID NUMBER (2),
Cost NUMBER (5,2),
Retail NUMBER (5,2),
Discount NUMBER (4,2),
Category VARCHAR2(12),
CONSTRAINT books_isbn_pk PRIMARY KEY(isbn),
CONSTRAINT books_pubid_fk FOREIGN KEY (pubid)
REFERENCES publisher (pubid));

INSERT INTO BOOKS
VALUES ('1059831198','BODYBUILD IN 10 MINUTES A DAY','21-JAN-05',4,18.75,30.95, NULL, 'FITNESS');
INSERT INTO BOOKS
VALUES ('0401140733','REVENGE OF MICKEY','14-DEC-05',1,14.20,22.00, NULL, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('4981341710','BUILDING A CAR WITH TOOTHPICKS','18-MAR-06',2,37.80,59.95, 3.00, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('8843172113','DATABASE IMPLEMENTATION','04-JUN-03',3,31.40,55.95, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('3437212490','COOKING WITH MUSHROOMS','28-FEB-04',4,12.50,19.95, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('3957136468','HOLY GRAIL OF ORACLE','31-DEC-05',3,47.25,75.95, 3.80, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('1915762492','HANDCRANKED COMPUTERS','21-JAN-05',3,21.80,25.00, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('9959789321','E-BUSINESS THE EASY WAY','01-MAR-06',2,37.90,54.50, NULL, 'COMPUTER');
INSERT INTO BOOKS
VALUES ('2491748320','PAINLESS CHILD-REARING','17-JUL-04',5,48.00,89.95, 4.50, 'FAMILY LIFE');
INSERT INTO BOOKS
VALUES ('0299282519','THE WOK WAY TO COOK','11-SEP-04',4,19.00,28.75, NULL, 'COOKING');
INSERT INTO BOOKS
VALUES ('8117949391','BIG BEAR AND LITTLE DOVE','08-NOV-05',5,5.32,8.95, NULL, 'CHILDREN');
INSERT INTO BOOKS
VALUES ('0132149871','HOW TO GET FASTER PIZZA','11-NOV-06',4,17.85,29.95, 1.50, 'SELF HELP');
INSERT INTO BOOKS
VALUES ('9247381001','HOW TO MANAGE THE MANAGER','09-MAY-03',1,15.40,31.95, NULL, 'BUSINESS');
INSERT INTO BOOKS
VALUES ('2147428890','SHORTEST POEMS','01-MAY-05',5,21.85,39.95, NULL, 'LITERATURE');

Explanation / Answer

CREATE OR REPLACE TRIGGER Book_message
BEFORE DELETE OR INSERT OR UPDATE ON Books
FOR EACH ROW
DECLARE
   v_ChangeType VARCHAR(100);

BEGIN
  
   IF INSERTING THEN
     v_ChangeType := 'Data Insert into table';
   ELSIF UPDATING THEN
     v_ChangeType := 'Data Update into table';
   ELSE
     v_ChangeType := 'Data Delete From table';
   END IF;

   DBMS_OUTPUT.put_line(v_ChangeType);
END
/

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

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