/*Prompt*/
Create a trigger to update the CUSTOMER_REPS table based on changes to service tickets.Every time a service ticket is updated to completed(i.e., IsCompletedchanges from 0 to 1), take care of the following requirements.
a)For thecorresponding customer representative, increment the YTD_Completed_Tickets valueby 1.Treat null values in ytd_completed_tickets as 0.
b)With the updated YTD_Completed_Tickets value, check the corredponding customer representative’s bonus eligibility. Every customer representative has the same annual quota they need to meet to receive an annual bonus. This quota is based onnumber of completed service tickets. For a customer representative, mark them as eligible for bonus if they’ve completed at least 5service tickets in the current year (otherwise ensure they are marked as not eligible).
c)Update the corresponding columnsin the CUSTOMER_REPS table(i.e., YTD_Completed_Tickets, Bonus_Eligible).
Notes: There might be other updates to the tuple that do not change the value of IsCompleted. (In that case, this trigger should not carry out the following tasks).
You can also assume that the Emp_ID (customer representative) associated with a service ticket will not change. You can also assume that a completed ticket will have a value for complete_date and Ticket_Date will not change to a different year.For simplicity, don’t worry about the cases where a service ticket “isCompleted” attribute changes from 1 to 0. Instead focus on the cases when isCompleted changes from 0 to 1.
Hint: review the use of the COALESCE() function to handle nulls.
/*Created tables and insert statements*/
Creating table CUSTOMER_REPS:
CREATE TABLE CUSTOMER_REPS
(
Rep_ID char (4),
Current_Assigned_Tickets number (2),
YTD_Completed_Tickets number (4),
YTD_Rating number (4,2),
Manager_ID char (4),
Bonus_Eligible number (1), -- recorded as 0 if not eligible, 1 if eligible
Contact varchar2(6), -- extension number
CONSTRAINT cust_rep_pk PRIMARY KEY (Rep_ID),
FOREIGN KEY (Rep_ID) REFERENCES EMPLOYEES (Emp_ID),
FOREIGN KEY (Manager_ID) REFERENCES Managers (Manager_ID),
CONSTRAINT CR_Bonus_Eligible CHECK (Bonus_Eligible IN (1,0))
);
Creating table SERVICE_TICKETS:
CREATE TABLE SERVICE_TICKETS
(
Ticket_ID char (8),
Ticket_Category char(10),
Ticket_Date DATE,
Player_ID char (7),
Game_ID char (3),
Emp_ID char(4),
IsCompleted number (1),
Complete_Date DATE,
Rating number (4,2),
CONSTRAINT Ticket_pk PRIMARY KEY (Ticket_ID),
FOREIGN KEY (Emp_ID) REFERENCES CUSTOMER_REPS (Rep_ID),
FOREIGN KEY (Game_ID) REFERENCES GAMES (Game_ID),
FOREIGN KEY (Player_ID) REFERENCES PLAYERS (Player_ID),
CONSTRAINT IsCompleted CHECK (IsCompleted IN (1,0)),
CONSTRAINT Complete_Date CHECK (Complete_Date >= Ticket_Date),
CONSTRAINT Rating_Date CHECK ((IsCompleted = 1 AND Rating IS NOT NULL AND Complete_Date IS NOT NULL) OR (IsCompleted = 0 AND Rating IS NULL AND Complete_Date IS NULL))
);
Insert record into the table:
insert into CUSTOMER_REPS values ('E001',1,0,4,'E005',0,'1234');
insert into CUSTOMER_REPS values ('E002',0,4,5.0,'E008',0,'2345');
insert into CUSTOMER_REPS values ('E003',0,1,4,'E005',0,'3456');
insert into CUSTOMER_REPS values ('E004',1,4,5,'E006',0,'5678');
insert into CUSTOMER_REPS values ('E007',0,0,null,'E006',0,'1245');
insert into service_tickets values ('T0000001', 'Server','02-OCT-2020','P000002','G04', 'E001',0, null, null);
insert into service_tickets values ('T0000002','Glitch','11-MAY-2020','P000004','G04','E002',1, '30-MAY-2020',5.0);
insert into service_tickets values ('T0000003','Glitch','04-SEP-2020','P000001','G02','E003',1, '05-SEP-2020',4);
insert into service_tickets values ('T0000004','Server','17-OCT-2020','P000002','G05','E004',0, null,null);
insert into service_tickets values ('T0000005','Server','20-JUL-2020','P000003','G03','E004',1, '21-JUL-2020',5.0);
insert into service_tickets values ('T0000006','Server','18-JUN-2020','P000001','G03','E004',1, '21-JUN-2020',5);
insert into service_tickets values ('T0000007','Glitch','13-FEB-2020','P000002','G05','E004',1, '14-FEB-2020',5);
insert into service_tickets values ('T0000008','Server','18-JAN-2020','P000003','G01','E004',1, '19-JAN-2020',5);
insert into service_tickets values ('T0000009','Glitch','20-AUG-2020','P000002','G05','E002',1, '30-AUG-2020',5.0);
insert into service_tickets values ('T0000010','Server','05-APR-2020','P000003','G05','E002',1, '06-APR-2020',5.0);
insert into service_tickets values ('T0000011','Glitch','28-JUL-2020','P000004','G05','E002',1, '29-JUL-2020',5.0);
/*Possible Outline (this is just what I created*/
CREATE SEQUENCE update_ticket START WITH 0 MAXVALUE 90 increment by 1;
CREATE OR REPLACE TRIGGER ticket_check
BEFORE INSERT OR UPDATE
ON CUSTOMER_REPS
FOR each ROW
DECLARE
BEGIN
SELECT
IF () THEN
END IF;
END;