Hey Guys I\'m stuck on creating two remaining simple triggers Like a delete row
ID: 3726897 • Letter: H
Question
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;
Explanation / Answer
--You need to create an audit table to use this trigger
/*Create table Student_audit (StudentID INT,
SchoolID CHAR(9),
PersonID INT,
deleted_date DATETIME,
deleted_by varchar2(20)*/
CREATE TRIGGER Student_after_delete_insert_update
AFTER DELETE,INSERT,UPDATE
ON Student FOR EACH ROW
BEGIN
DECLARE vUser varchar(50);
-- Find username of person performing the DELETE into table
SELECT USER() INTO vUser;
-- Insert record into audit table
INSERT INTO Student_audit
( StudentID,
SchoolID,
PersonID,
deleted_date,
deleted_by)
VALUES
( OLD.contact_id,OLD.SchoolID,OLD.PersonID,
SYSDATE(),
vUser );
END;
--/*********Update corresponding table Holds customerID*********/
create trigger Student_Update_delete after UPDATE ON Student
FOR EACH ROW
BEGIN UPDATE
student INNER JOIN Holds
ON student.StudentID=Holds.StudentID
SET Holds.StudentID=student.StudentID ;
END;
/******************comment if you have any doubt*******************/