728 Assignment 05

ASSIGNMENT-05
NAME-ROHIT KUMAR
REG.NO.- 728
ROLL NO.-CSE/21068
SUBJECT CODE- CSC613
EMAIL:- cse21068@iiitkalyani.ac.in
Prob 1:
schemas:
person (SSN, Name, Department)
works_under (SSN, MGR_SSN)
wants_to_work_under (SSN, MGR_SSN)
Tables:
Queries:
a) Write trigger(s) to check if (A, B) is there in works_under then (A, C) cannot be the added to
works_under (one employee can work under one manager only).
CREATE OR REPLACE TRIGGER trigger1

BEFORE INSERT ON works_under
FOR EACH ROW
DECLARE
mgr_count NUMBER;
BEGIN
SELECT COUNT(*) INTO mgr_count FROM works_under WHERE SSN = :new.SSN;
IF mgr_count > 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'One employee can work under one manager only');
END IF;
END;
/
INSERT INTO works_under VALUES (10005,10006);
Result:-
b) Write a trigger that automatically changes the department name of A by the name of the department
of B whenever (A, B) is inserted to works_under (manager’s department is the employee’s department).

AFTER INSERT ON works_under
FOR EACH ROW
BEGIN
UPDATE person SET Department = (SELECT Department FROM person WHERE SSN =
:new.MGR_SSN) WHERE SSN = :new.SSN;
END;
/
INSERT INTO works_under VALUES (10007,10003);
SELECT * FROM person;
SELECT * FROM works_under;
Result:-
c) Write a trigger to enforce the following: Assume that currently A’s manager is C. If A wants to work
under B and (A, B) is inserted to wants_to_work_under then immediately (A, C) will be deleted from
works_under. Department names will be updated accordingly.
BEFORE INSERT ON wants_to_work_under
FOR EACH ROW
DECLARE
old_mgr_ssn NUMBER;
BEGIN
SELECT MGR_SSN INTO old_mgr_ssn FROM works_under WHERE SSN = :new.SSN;
IF old_mgr_ssn IS NOT NULL AND old_mgr_ssn <> :new.MGR_SSN THEN
DELETE FROM works_under WHERE SSN = :new.SSN AND MGR_SSN = old_mgr_ssn;

UPDATE person SET Department = (SELECT Department FROM person WHERE SSN =
:new.MGR_SSN) WHERE SSN = :new.SSN;
END IF;
END;
/
INSERT INTO wants_to_work_under VALUES(10005,10006);

SELECT * FROM person;
SELECT * FROM works_under;

SELECT * FROM wants_to_work_under;
Result:-
Prob 2:
schemas:
CUSTOMER (SSN, Name, Surname, PhoneNum, Plan_Code)
PLAN (Plan_Code, ConnectionFee, PricePerSecond ) /* The connection fee is onetime payable when
the customer is registered. */
PHONE_CALL (SSN, Date, Time, CalledNum, Seconds)
BILL (SSN, Month, Year, amount ) /* Monthly bills */
Tables:-
Queries :
a) Write a trigger to update the customer's bill after each phone call. Assume that every customer has
a bill amount at any point of time. You can create another trigger that creates a bill with Rs 0.0 for
initialization for each registered customer. This can happen when the customer's details are entered
into the customer table OR for existing customers when the month begins, i.e., on the first day of
every month at 12:01 AM.
CREATE OR REPLACE TRIGGER update_bill

AFTER INSERT ON PHONE_CALL
FOR EACH ROW
DECLARE
record INT;
BEGIN
SELECT COUNT(*) INTO record FROM BILL WHERE SSN = :new.SSN AND Month = EXTRACT(MONTH
FROM :new.Dates) AND Year = EXTRACT(YEAR FROM :new.Dates);
IF record = 0 THEN
INSERT INTO BILL VALUES(:new.SSN,EXTRACT(MONTH FROM :new.Dates),EXTRACT(YEAR FROM
:new.Dates),0.0);
END IF;
UPDATE BILL
SET Amount = Amount + (SELECT :new.Seconds*p.PricePerSecond FROM PLAN p,CUSTOMER c
WHERE c.SSN = :new.SSN AND p.Plan_Code = c.Plan_Code)
WHERE SSN = :new.SSN AND Month = EXTRACT(MONTH FROM :new.Dates) AND Year =
EXTRACT(YEAR FROM :new.Dates);
END;
/
INSERT INTO PHONE_CALL VALUES('107', TO_DATE('2023-03-30', 'YYYY-MM-DD'),
TO_TIMESTAMP('2023-03-30 09:30:00', 'YYYY-MM-DD HH24:MI:SS'), '90-421', 113);
SELECT * FROM BILL;
Result:-
b) Write a trigger so that when a customer’s plan is changed (i.e., edited Plan_Code in the Customer
table), it should create a message showing the extra cost to be added in the bill against the
ConnectionFee and the current bill should be displayed.
CREATE OR REPLACE TRIGGER plan_change
AFTER UPDATE OF Plan_Code ON CUSTOMER
FOR EACH ROW
DECLARE
extra_cost NUMBER(10,2);
current_bill NUMBER(10,2);
BEGIN
IF :new.Plan_Code <> :old.Plan_Code THEN
SELECT ConnectionFee INTO extra_cost FROM PLAN WHERE Plan_Code = :new.Plan_Code;
UPDATE BILL
SET Amount = Amount + extra_cost
WHERE SSN = :new.SSN AND Month = EXTRACT(MONTH FROM SYSDATE) AND Year =
EXTRACT(YEAR FROM SYSDATE);
SELECT Amount INTO current_bill FROM BILL WHERE SSN = :new.SSN AND Month =
EXTRACT(MONTH FROM SYSDATE) AND Year = EXTRACT(YEAR FROM SYSDATE);
DBMS_OUTPUT.PUT_LINE('Extra cost for plan change: Rs ' || extra_cost);

DBMS_OUTPUT.PUT_LINE('Current bill: Rs ' || current_bill);
END IF;
END;
/
UPDATE CUSTOMER
SET Plan_Code = 'P002' WHERE SSN = '101';
Result:-
c) Write a trigger to check the bill amount after every call. If the amount crosses Rs. 2000 then
PricePerSecond drops by 50% in the same plan for that customer.

AFTER INSERT ON PHONE_CALL
FOR EACH ROW
DECLARE
current_amt NUMBER;
BEGIN
SELECT Amount INTO current_amt FROM BILL
IF current_amt >= 2000 THEN

DBMS_OUTPUT.PUT_LINE('CURRENT BILL AMT GREATER THAN 2000');
UPDATE BILL
SET Amount = Amount + (SELECT :new.Seconds*p.PricePerSecond*0.5 FROM PLAN p,CUSTOMER
c WHERE c.SSN = :new.SSN AND p.Plan_Code = c.Plan_Code)
ELSE
DBMS_OUTPUT.PUT_LINE('CURRENT BILL AMT LESS THAN 2000');
UPDATE BILL
END IF;
END;
/
SELECT * FROM BILL;
Result:-
d) Write a trigger to check: Assume that five-digit phone numbers are being used, and the first two
digits will identify the service provider. If the customer calls the numbers of the same company, i.e., if
the first two digits of PhoneNum are the same as the first two digits of CalledNum then the
PricePerSecond will be applicable with a 50% discount.

BEFORE INSERT ON PHONE_CALL
FOR EACH ROW
DECLARE
customer_provider VARCHAR2(2);
called_provider VARCHAR2(2);
BEGIN
SELECT SUBSTR(PhoneNum, 1, 2) INTO customer_provider FROM CUSTOMER WHERE SSN = :new.SSN;
called_provider := SUBSTR(:new.CalledNum, 1, 2);
IF customer_provider = called_provider THEN

DBMS_OUTPUT.PUT_LINE('SAME SERVICE PROVIDERS : ' || customer_provider);
UPDATE BILL
SET Amount = Amount + (SELECT :new.Seconds*p.PricePerSecond*0.5 FROM PLAN p,CUSTOMER c
WHERE SSN = :new.SSN AND Month = EXTRACT(MONTH FROM SYSDATE) AND Year = EXTRACT(YEAR
FROM SYSDATE);
ELSE
DBMS_OUTPUT.PUT_LINE('DIFFERENT SERVICE PROVIDERS');
UPDATE BILL
WHERE SSN = :new.SSN AND Month = EXTRACT(MONTH FROM SYSDATE) AND Year = EXTRACT(YEAR
FROM SYSDATE);
END IF;
END;
/
SELECT * FROM BILL;
Result:-

728 Assignment 05

Uploaded by

Copyright:

Available Formats

728 Assignment 05

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

728 Assignment 05

Uploaded by

Copyright:

Available Formats

ASSIGNMENT-05

works_under (one employee can work under one manager only).

CREATE OR REPLACE TRIGGER trigger1

of B whenever (A, B) is inserted to works_under (manager’s department is the employee’s department).

DELETE FROM works_under WHERE SSN = :new.SSN AND MGR_SSN = old_mgr_ssn;

INSERT INTO wants_to_work_under VALUES(10005,10006);

SELECT * FROM works_under;

CREATE OR REPLACE TRIGGER update_bill

SELECT ConnectionFee INTO extra_cost FROM PLAN WHERE Plan_Code = :new.Plan_Code;

DBMS_OUTPUT.PUT_LINE('Extra cost for plan change: Rs ' || extra_cost);

CREATE OR REPLACE TRIGGER update_bill

IF current_amt >= 2000 THEN

CREATE OR REPLACE TRIGGER update_bill

IF customer_provider = called_provider THEN

You might also like