I am doing an assignment for triggers in SQL Server. The detailed assignment problem is:
Faculty Head needs to be an instructor that belongs to his/her corresponding faculty and has degree either as "VICEDOCTOR" or "DOCTOR"
I am writing a trigger set containing a total 4 sub-triggers, 3 cases for when the condition is not satisfied and 1 last case when the condition is satisfied.
Here is my SQL Server code:
USE master
go
ALTER DATABASE [DepartmentManagement]
SET single_user WITH ROLLBACK IMMEDIATE
DROP DATABASE [DepartmentManagement]
CREATE DATABASE DepartmentManagement
GO
SET DATEFORMAT DMY
GO
USE DepartmentManagement
CREATE TABLE INSTRUCTOR
(
INSTRUCTORID char(4) PRIMARY KEY,
FULLNAME varchar(40),
DEGREE varchar(10),
INSTRUCTORRANK varchar(10),
SEX varchar(10),
DATEOFBIRTH smalldatetime,
STARTDAY smalldatetime,
COEFFICIENT numeric(4,2),
SALARY money,
FACULTYID varchar(4)
)
GO
CREATE TABLE FACULTY
(
FACULTYID varchar(4) PRIMARY KEY,
FACULTYNAME varchar(40) NOT NULL,
FOUNDINGDAY smalldatetime NOT NULL,
FACULTYHEAD char(4) NOT NULL
)
GO
INSERT INTO INSTRUCTOR
VALUES ('GV01', 'Andy White', 'VICEDOCTOR', 'PROFESSOR', 'MALE', '2/5/1950', '11/1/2004', '5', '2,250,000', 'CS')
INSERT INTO INSTRUCTOR
VALUES ('GV05', 'Walter Brock', 'DOCTOR', 'PROFESSOR', 'MALE', '12/3/1958', '12/1/2005', '3', '1,350,000', 'IS')
GO
CREATE TRIGGER trg_ins_facultyhead
ON FACULTY
AFTER INSERT
AS
BEGIN
IF EXISTS (SELECT *
FROM INSERTED, INSTRUCTOR -- case #1: correct FACULTY but not DEGREE
WHERE INSERTED.FACULTYHEAD = INSTRUCTOR.INSTRUCTORID
AND INSERTED.FACULTYID = INSTRUCTOR.FACULTYID
AND DEGREE != 'VICEDOCTOR'
AND DEGREE != 'DOCTOR')
BEGIN
-- ERROR --
RAISERROR('ERROR: FACULTY HEAD NEEDS TO HAVE DEGREE AS EITHER ''VICE DOCTOR'' OR ''DOCTOR'' ', 16, 1)
-- RESTORE TO PREVIOUS STAGE---
ROLLBACK TRANSACTION
END
IF EXISTS (SELECT *
FROM INSERTED, INSTRUCTOR -- Case #2: both degree and faculty do not satisfy
WHERE INSERTED.FACULTYHEAD = INSTRUCTOR.INSTRUCTORID
AND INSERTED.FACULTYID != INSTRUCTOR.FACULTYID
AND DEGREE != 'VICEDOCTOR'
AND DEGREE != 'DOCTOR')
BEGIN
-- ERROR --
RAISERROR('ERROR: FACULTY HEAD NEEDS TO BELONG TO CORRESPONDING FACULTY AND DEGREE AS EITHER ''VICE DOCTOR'' OR ''DOCTOR'' ', 16, 1)
-- RESTORE TO PREVIOUS STAGE --
ROLLBACK TRANSACTION
END
IF EXISTS (SELECT *
FROM INSERTED, INSTRUCTOR -- Case #3: correct DEGREE but not FACULTY
WHERE INSERTED.FACULTYHEAD = INSTRUCTOR.INSTRUCTORID
AND INSERTED.FACULTYID != INSTRUCTOR.FACULTYID
AND DEGREE = 'VICEDOCTOR' OR DEGREE = 'DOCTOR')
BEGIN
-- ERROR --
RAISERROR('ERROR: FACULTY HEAD NEEDS TO BELONG TO CORRESPONDING FACULTY ', 16, 1)
-- RESTORE TO PREVIOUS STAGE --
ROLLBACK TRANSACTION
END
IF EXISTS (SELECT *
FROM INSERTED, INSTRUCTOR -- Case #4: both FACULTY and DEGREE satisfy
WHERE INSERTED.FACULTYHEAD = INSTRUCTOR.INSTRUCTORID
AND INSERTED.FACULTYID = INSTRUCTOR.FACULTYID
AND DEGREE = 'VICEDOCTOR' OR DEGREE = 'DOCTOR')
BEGIN
-- SUCCESS --
PRINT 'ADDED FACULTY HEAD SUCCESSFULLY!'
END
END
GO
INSERT INTO FACULTY
VALUES ('CS', 'Computer Science', '7/6/2005', 'GV01')
GO
SELECT *
FROM FACULTY, INSTRUCTOR
The problem starts at the 3rd case. When I do insert into FACULTY values ('CS','Computer Science','7/6/2005','GV01') GO, the 3rd case trigger fired though the condition do not match at all. I expect it to be the last case, which is inserted successfully without any errors.
I then delete
INSERT INTO INSTRUCTOR
VALUES ('GV05', 'Walter Brock', 'DOCTOR', 'PROFESSOR', 'MALE', '12/3/1958', '12/1/2005', '3', '1,350,000', 'IS')
and it works as I expected.
Then I tried replacing
INSERT INTO FACULTY
VALUES ('CS', 'Computer Science', '7/6/2005', 'GV01')
GO
with
INSERT INTO FACULTY
VALUES ('IS', 'Information System', '7/6/2005', 'GV05')
and then delete
INSERT INTO INSTRUCTOR
VALUES ('GV05', 'Walter Brock', 'DOCTOR', 'PROFESSOR', 'MALE', '12/3/1958', '12/1/2005', '3', '1,350,000', 'IS')
and it works without having error case trigger fired, I then check the result table and it seems the inserted table and Instructor table are joined though the ID condition do not match. Which mess up the joined tables and cause the unwanted trigger to fire.
So could anyone explain how are the tables still joined though the
condition WHERE INSERTED.FACULTYHEAD = INSTRUCTOR.INSTRUCTORID do not satisfy?
And how can I fix this joined table issue and make the triggers run correctly?
Thanks for the help in advance!
Your primary issue is the OR which has a lower precedence than AND. You need to enclose AND (DEGREE ='VICEDOCTOR' OR DEGREE = 'DOCTOR') in brackets, or use IN like AND DEGREE IN ('VICEDOCTOR', 'DOCTOR').
But you are completely over-complicating this. You don't need four separate checks, you only need two. Whichever error throws first will give the error.
Further notes:
DEGREE being null.UPDATE as well.SET NOCOUNT ON to prevent spurious client-side resultsets, and improve performance.THROW rather than RAISERROR and ROLLBACK, as this throws the error properly, rather than giving the client a strange rollback error.FACULTYHEAD needs to be nullable, otherwise you could never create the FACULTY at all.CREATE TRIGGER trg_facultyhead ON FACULTY
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
IF EXISTS( SELECT 1
FROM INSERTED i
JOIN INSTRUCTOR ins ON i.FACULTYHEAD = ins.INSTRUCTORID
WHERE (ins.DEGREE NOT IN ('VICEDOCTOR', 'DOCTOR') OR ins.DEGREE IS NULL)
)
THROW 50001, N'ERROR: FACULTY HEAD NEEDS TO HAVE DEGREE AS EITHER ''VICE DOCTOR'' OR ''DOCTOR'' ', 1;
--- THROW handles rollback
IF EXISTS( SELECT 1
FROM INSERTED i
JOIN INSTRUCTOR ins ON i.FACULTYHEAD = ins.INSTRUCTORID
WHERE i.FACULTYID != ins.FACULTYID
)
THROW 50002, N'ERROR: FACULTY HEAD NEEDS TO BELONG TO CORRESPONDING FACULTY', 1;
-- THROW handles rollback
GO
Having said that, I would actually enforce this with constraints instead of a trigger.
CREATE TABLE INSTRUCTOR
(
INSTRUCTORID char(4) primary key,
FULLNAME varchar(40),
DEGREE varchar(10),
INSTRUCTORRANK varchar(10),
SEX varchar(10),
DATEOFBIRTH smalldatetime,
STARTDAY smalldatetime,
COEFFICIENT numeric(4,2),
SALARY money,
FACULTYID varchar(4) NOT NULL,
INDEX IX_FACULTY UNIQUE (INSTRUCTORID, FACULTYID)
);
CREATE TABLE FACULTY
(
FACULTYID varchar(4) primary key,
FACULTYNAME varchar(40) NOT NULL,
FOUNDINGDAY smalldatetime NOT NULL,
FACULTYHEAD char(4),
FOREIGN KEY (FACULTYHEAD, FACULTYID) REFERENCES INSTRUCTOR (INSTRUCTORID, FACULTYID)
);
ALTER TABLE INSTRUCTOR
ADD CONSTRAINT fk_faculty FOREIGN KEY (FACULTYID) REFERENCES FACULTY (FACULTYID);
INSERT INTO FACULTY (FACULTYID, FACULTYNAME, FOUNDINGDAY)
VALUES ('CS', 'Computer Science', '7/6/2005');
INSERT INTO INSTRUCTOR
VALUES ('GV01', 'Andy White', 'VICEDOCTOR', 'PROFESSOR', 'MALE', '2/5/1950', '11/1/2004', '5', '2,250,000', 'CS'),
VALUES ('GV05', 'Walter Brock', 'DOCTOR', 'PROFESSOR', 'MALE', '12/3/1958', '12/1/2005', '3', '1,350,000', 'CS');
Note the addition of the unique index/constraint on INSTRUCTOR, this allows us to place a composite FK to it from FACULTY, enforcing the first condition.
For the second condition, we can utilize a little-known trick in SQL Server for multi-table constraints. We can also use this for the first condition if necessary.
Create a dummy table of two rows.
CREATE TABLE DummyTwoRows (dummy int NOT NULL);
INSERT DummyTwoRows VALUES (1),(1);
Create a view representing the rows we don't want to exist.
CREATE VIEW dbo.vFacultyHeadMustBeDoctors
WITH SCHEMABINDING
AS
SELECT
f.FACULTYHEAD,
i.INSTRUCTORID,
d.dummy
FROM dbo.FACULTY f
JOIN dbo.INSTRUCTOR i ON f.FACULTYHEAD = i.INSTRUCTORID
CROSS JOIN dbo.DummyTwoRows d
WHERE (i.DEGREE NOT IN ('VICEDOCTOR', 'DOCTOR') OR i.DEGREE IS NULL);
Then index it as a materialized view. Note that this view is always empty by definition. Any attempt to insert an incorrect row will fail with a unique key violation.
CREATE UNIQUE CLUSTERED INDEX CX ON vFacultyHeadMustBeDoctors (dummy);