Lab 6.sql

USE CSE_4B_369

--Trigger (After trigger)
--Table : Log(LogMessage varchar(100), logDate Datetime)

CREATE TABLE LOG(
LOGMESSAGE VARCHAR(100),
LOGDATE DATETIME
)


--Part – A
--1. Create trigger for printing appropriate message after student registration.

CREATE OR ALTER TRIGGER TR_STU_REG
ON STUDENT
AFTER INSERT
AS
BEGIN
	PRINT('THANK YOU FOR REGISTRATION')
END

DROP TRIGGER TR_STU_REG
--2. Create trigger for printing appropriate message after faculty deletion.

CREATE OR ALTER TRIGGER TR_FAC_DELETE
ON FACULTY
AFTER DELETE
AS
BEGIN
	PRINT('DELETION IS SUCESSFUL')
END

DROP TRIGGER TR_FAC_DELETE

--3. Create trigger for monitoring all events on course table. (print only appropriate message)

CREATE OR ALTER TRIGGER TR_EVENT_MONIT
ON COURSE
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
	PRINT('SOME EVENT HAPPEN IN COURSE TABLE')
END

DROP TRIGGER TR_EVENT_MONIT

--4. Create trigger for logging data on new student registration in Log table.

CREATE OR ALTER TRIGGER TR_LOGGING_DATA
ON STUDENT
AFTER INSERT
AS
BEGIN
	DECLARE @SID INT
	SELECT @SID = StudentID FROM inserted
	INSERT INTO LOG VALUES(CONCAT('STUDENT WITH ID', @SID), GETDATE())
END

DROP TRIGGER TR_LOGGING_DATA

--5. Create trigger for auto-uppercasing faculty names.

CREATE OR ALTER TRIGGER TR_AUTO_UPPERCASE
ON FACULTY
AFTER INSERT
AS
BEGIN
	UPDATE FACULTY 
	SET FacultyName = UPPER(FacultyName)
	WHERE FacultyID = (SELECT FacultyID FROM inserted)
END

DROP TRIGGER TR_AUTO_UPPERCASE

--6. Create trigger for calculating faculty experience (Note: Add required column in faculty table)

ALTER TABLE FACULTY
ADD FACULTYEXPERIENCE INT

CREATE OR ALTER TRIGGER TR_FACULT_EXP
ON FACULTY
AFTER INSERT
AS
BEGIN
	UPDATE FACULTY 
	SET FACULTYEXPERIENCE = DATEDIFF(YEAR, FacultyJoiningDate, GETDATE())
	WHERE FacultyID = (SELECT FacultyID FROM inserted)
END

DROP TRIGGER TR_FACULT_EXP



--Part – B

--7. Create trigger for auto-stamping enrollment dates.

CREATE OR ALTER TRIGGER TR_AUTO_STAMPING
ON ENROLLMENT
AFTER INSERT
AS
BEGIN
	UPDATE ENROLLMENT 
	SET EnrollmentDate = GETDATE()
	WHERE EnrollmentID = (SELECT EnrollmentID FROM inserted)
END

DROP TRIGGER TR_AUTO_STAMPING

--8. Create trigger for logging data After course assignment - log course and faculty detail.

CREATE TABLE LOGDETAIL(
CNAME VARCHAR(50),
FNAME VARCHAR(50),
ADATE DATE
)

CREATE OR ALTER TRIGGER TR_AFTER_COURSE_ASSIGNMENT
ON COURSE_ASSIGNMENT 
AFTER INSERT
AS
BEGIN
	DECLARE @CID INT
	DECLARE @FID INT
	DECLARE @CNAME VARCHAR(100)
	DECLARE @FNAME VARCHAR(100)
	SELECT @CID=CourseID,@FID=FacultyID FROM INSERTED
	SELECT @CNAME = CourseName FROM COURSE WHERE CourseID = @CID
	SELECT @FNAME = FacultyName FROM FACULTY WHERE FacultyID = @FID
	INSERT INTO LOGDETAIL VALUES (@CNAME,@FNAME,GETDATE())
END

DROP TRIGGER TR_AUTO_STAMPING

--Part - C
--9. Create trigger for updating student phone and print the old and new phone number.



--10. Create trigger for updating course credit log old and new credits in log table.