Hey Guys I'm stuck on creating two remaining simple triggers Like a delete row on student table or update room or join and update etc on the bottom of my sql code. If anyone can give me a hand with that an maybe help me correct any errors I would appreciate all the help I can get. This is a final project I'm turning in and I would mind any help I can get reviewing it. Thank you all very much for taking the time to assist. --Create database statement Create database AdoptABook

CREATE TABLE Person ( PersonID int IDENTITY NOT NULL, FirstName varchar(50) NOT NULL, LastName varchar(50) NOT NULL, Email varchar(50) NOT NULL, PhoneNumber varchar(15) NULL, CONSTRAINT PersonID_PK PRIMARY KEY (PersonID));

CREATE TABLE Employee ( EmployeeID int IDENTITY NOT NULL, Position varchar(50) NOT NULL, PersonID int NOT NULL, CONSTRAINT EmployeeID_PK PRIMARY KEY (EmployeeID), CONSTRAINT PersonID_FK FOREIGN KEY (PersonID) REFERENCES Person (PersonID));

CREATE TABLE Student ( StudentID int IDENTITY NOT NULL, SchoolID char(9) NOT NULL, PersonID int, CONSTRAINT StudentID_PK PRIMARY KEY (StudentID));

CREATE TABLE Inventory ( InventoryID int IDENTITY NOT NULL, Status varchar(20) NULL, CONSTRAINT InventoryID_PK PRIMARY KEY (InventoryID));

CREATE TABLE Holds ( HoldID int IDENTITY NOT NULL, InventoryID Int, StudentID Int, TimeOfRequest date NULL, CONSTRAINT HoldID_PK PRIMARY KEY (HoldID), CONSTRAINT InventoryID_FK FOREIGN KEY (InventoryID) REFERENCES Inventory (InventoryID), CONSTRAINT StudentID_FK FOREIGN KEY (StudentID) REFERENCES Student (StudentID));

CREATE TABLE Payment ( PayID int IDENTITY NOT NULL, PaymentMethod varchar(6) NOT NULL, DateOfPurchase date NULL, CONSTRAINT PayID_PK PRIMARY KEY (PayID));

CREATE TABLE Course ( CourseID int IDENTITY NOT NULL, CourseTitle varchar(100) NOT NULL, CourseTypeID varchar(6) NOT NULL, CRN int NOT NULL, CONSTRAINT CourseID_PK PRIMARY KEY (CourseID));

CREATE TABLE BookPlate ( BookPlateID int IDENTITY NOT NULL, Price decimal(19, 0) NOT NULL, CourseID int, PayID int, StudentID int, CONSTRAINT BookPlateID_PK PRIMARY KEY (BookPlateID), CONSTRAINT CourseID_FK FOREIGN KEY (CourseID) REFERENCES Course (CourseID), CONSTRAINT PayID_FK FOREIGN KEY (PayID) REFERENCES Payment (PayID));

Create TABLE Book ( BookID int IDENTITY NOT NULL, ISBN varchar(13) NOT NULL, BookTitle varchar(100) NOT NULL, Edition varchar(50) NULL, Author varchar(100) NULL, CourseID int, BookPlateID int, InventoryID int, CONSTRAINT BookID_PK PRIMARY KEY (BookID), CONSTRAINT BookPlateID_FK FOREIGN KEY (BookPlateID) REFERENCES BookPlate (BookPlateID));

---------------------------------------------------------------- ---------CREATE INDEXES-----------------------------------------

CREATE INDEX IX_Book_CourseID ON Book (CourseID);

CREATE INDEX IX_BookPlate_StudentID ON BookPlate (StudentID);

CREATE INDEX IX_Course_CourseID ON Course (CourseID);

CREATE INDEX IX_Book_ISBN ON Book (ISBN); ---SET DATABASE TABLES---------/

----------------------------/ -------Sequence Statement

CREATE SEQUENCE BookIDSequence START WITH 900 INCREMENT BY 1;

----------------------------/ SET IDENTITY_INSERT Person ON;

INSERT INTO person (PersonID, FirstName, LastName, Email, [PhoneNumber]) Values (101, 'Felicia', 'Bye', 'ByeFelicia@Internet.com', '5038886652'), (102, 'Roger', 'Moore', 'RMoore@yahoo.net', '8086654410'), (103, 'Crystal', 'Trump', 'Notrelatedtodaprez@hughesnet.net', '3605203312'), (104, 'Peggy', 'Sue', 'PSue@oit.edu', '5037782245'), (105, 'William', 'Walaby', 'Wwalaby@zenith.org', '5418880874');

SET IDENTITY_INSERT Person OFF; SET IDENTITY_INSERT Employee ON;

INSERT INTO Employee (EmployeeID, Position, PersonID) Values (201, 'Student President', 101), (202, 'Student Vice President', 102), (203, 'Receptionist', 103), (204, 'Club Officer ', 104), (205, 'Money Officer', 105);

SET IDENTITY_INSERT Employee OFF; SET IDENTITY_INSERT Student ON;

INSERT INTO Student (StudentID, SchoolID, PersonID) Values (301,'918445223' ,101), (302, '918775001',102), (303, '918001223',103), (304, '918445330',104), (305, '918550444',105);

SET IDENTITY_INSERT Student OFF; SET IDENTITY_INSERT Inventory ON;

INSERT INTO Inventory (InventoryID, Status) Values (401, 'Reserved'), (402, 'On Hold'), (403, 'Purchased'), (404, 'Reserved'), (405, 'Available');

SET IDENTITY_INSERT Inventory OFF; SET IDENTITY_INSERT Holds ON;

INSERT INTO Holds (HoldID, InventoryID, StudentID, TimeOfRequest) Values

(501, 401, 301, '2018-10-05'), (502, 401, 301, '2018-03-05'), (503, 403, 303, '2018-01-01'), (504, 404, 304, '2018-11-11'), (505, 405, 305, '2019-07-04');

SET IDENTITY_INSERT Holds OFF; SET IDENTITY_INSERT Payment ON;

INSERT INTO Payment (PayID, PaymentMethod, DateOfPurchase) Values (601, 'Cash', '2018-02-04'), (602, 'Paypal', '2018-11-05'), (603, 'Cash', '2018-04-04'), (604, 'Cash', '2018-05-01'), (605, 'Paypal', '2018-06-06');

SET IDENTITY_INSERT Payment OFF SET IDENTITY_INSERT Course ON;

INSERT INTO Course (CourseID, CourseTitle, CourseTypeID, CRN) Values (701, 'Basket Weaving', 'BSW101', '334201'), (702, 'Systems Analysis', 'MIS312', '54670'), (703, 'Business Intelligence', 'MIS410', '47860'), (704, 'Being Awesome', 'BAW101', '66666'), (705, 'Computer Forensics', 'MIS407', '67805');

SET IDENTITY_INSERT Course OFF; SET IDENTITY_INSERT BookPlate ON;

INSERT INTO BookPlate (BookPlateID, CourseID, PayID, StudentID, Price) Values (801, 701, 605, 301, '35'), (802, 702, 601, 302, '65'), (803, 703, 601, 303, '40'), (804, 704, 603, 304, '15'), (805, 705, 604, 305, '50');

SET IDENTITY_INSERT BookPlate OFF; SET IDENTITY_INSERT Book ON;

INSERT INTO Book (BookID, CourseID, ISBN, BookTitle, Edition, Author, BookPlateID, InventoryID) Values (901, 701, '8888666522001', 'Im an basket artist', 'Eighth Edition', 'Sam Engelwood', 801, 401), (902, 702, '7778223167890', 'Making things work', 'Fifth Ediiton', 'Steve Murach', 802, 402), (903, 703, '5678000133298', 'Database Analysis', 'First Edition', 'Lindy Hung', 803, 403), (904, 704, '5590345677878', 'Everything is Awesome', 'Tenth Edition', 'Peggy Brockcamp', 804, 404), (905, 705, '1334008922144', 'Computer Deep Dive', 'Fourth Edition', 'Kris Rosenburg', 805, 405);

SET IDENTITY_INSERT Book OFF;

---------SET Constraints after dataBase is Built

ALTER TABLE Student ADD CONSTRAINT Chk_SchoolID CHECK (SchoolID Like '[198][0-9][0-9][0-9][0-9][0-9][0-9]');

ALTER TABLE Person ADD CONSTRAINT Chk_PhoneNumber CHECK (PhoneNumber like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]');

Select * From Student

Select * From Book

Select * From course

Select * From Employee

Select * From Holds

Select * From Inventory

Select * From Payment

Select * From Person

Select * From BookPlate

--Procedure

CREATE PROC spDisplayStudent AS SELECT StudentID, SchoolID, PersonID FROM Student; GO exec spDisplayStudent

CREATE PROC spAddStudent ( @StudentID int, @SchoolID char(9), @PersonID int ) AS BEGIN TRY Insert Student VALUES (@StudentID, @SchoolID, @PersonID); PRINT'You added some stuff to students GOOD JOB.' END TRY BEGIN CATCH PRINT 'An error showed up. Course failed to add.'; PRINT '5001:' + CONVERT(varchar, ERROR_NUMBER()); PRINT 'Error Message:' + CONVERT(varchar, ERROR_MESSAGE()); END CATCH;

GO

CREATE PROC spUpdatePayment ( @PayID int, @PaymentMethod varchar(6), @DateOfPurchase date ) AS BEGIN TRY IF @PayID = '601' and @DateOfPurchase = '2018-02-04' Update Payment Set @PaymentMethod = 'Cash'

ELSE IF @PayID = '602' and @DateOfPurchase = '2018-11-05' Update Payment Set @PaymentMethod = 'Paypal'

ELSE IF @PayID = '603' and @DateOfPurchase = '2018-04-04' Update Payment Set @PaymentMethod = 'Cash'

ELSE IF @PayID = '604' and @DateOfPurchase = '604' Update Payment Set @PaymentMethod = 'Cash' WHERE PayID = @PayID Print 'Successful payment update.' END TRY BEGIN CATCH PRINT 'ERROR OCCURED, NO UPDATE.'; PRINT '5001:' + CONVERT(varchar, ERROR_MESSAGE()); PRINT 'Error Message:' + convert(varchar, ERROR_MESSAGE()); END CATCH;

GO

Create Proc --Functions Create function fnGetStudents() RETURNS int Begin RETURN (SELECT COUNT(SchoolID) FROM Student); END; print 'Total number of students.' + convert(varchar, dbo.fnGetStudentns());

GO -- Create Function fnHowManyPresidents() Returns int Begin Return (SELECT COUNT (DISTINCT Position) as StudentPresident FROM Employee);

END;

GO

Create function fnBookCount() Returns int Begin Return (Select Count (ISBN) as BookCount from Book); End;

GO

USE AdoptABook GO

Create Trigger DuplicateStudentInfo On Student AFTER INSERT, UPDATE AS Begin If (Select count(*) From Student Join inserted On Student.SchoolID = inserted.SchoolID) > 1 Begin Rollback tran; Throw 50001,'Duplicate value dummy.', 1 End; End;