Question

I have tables student, professor, and person. The relationship between student and person, and professor and person is Is A inheritance. A person may be a student but not a professor, a professor but not a student, or neither a professor or a student. How do I write a trigger in SQL that forces the constraint that a person cannot be a student and a professor.

Some extra info:

I have defined ID to be PK of person. ID is also PK of professor and student as a FK (for the inheritance)

Answer 1

from the given data the code to create a trigger in SQL is:

first, to create a trigger the syntax is

CREATE TRIGGER [trigger_name] -------------> this indicates the triiger name
[before | after] -------------> this explains about the execution of trigger
{insert | update | delete} -----> this explains about the operations in DML.
on [table_name] ------> this explains about the table name that conneted with the trigger.
[for each row] -----> this explains the tirgger that executes in each row.
[trigger_body] ------> this explains about the operation that performs when trigger is fried.


now from the given data, the tables are student, professor, and person.
next, the relationship between the student and person, and professor and person is inheritance
next A person can be a student but not a professor, a professor but not a student, neither professor nor a student.

next given that,
Person ID is the primary key in the person table.
Professor ID is the primary key in the professor's table.
student as a foreign key for inheritance.

now the trigger in SQL that forces the constraint that a person cannot be a student and a professor is:



CREATE TRIGGER Person
AFTER INSERT, UPDATE
AS
IF EXISTS (
SELECT *
FROM Professor AS P
INNER JOIN (
SELECT Professor_ID, COUNT(*) AS Person_ID
FROM Person
GROUP
BY Professor_ID
) AS R
ON R.Professor_ID ≠ P.Professor_ID
AND P.Person_ID ≠ R.Person_ID
)
BEGIN
RAISERROR ('A PERSON CANNOT BE A STUDENT AND A PROFESSOR);
ROLLBACK TRANSACTION;
RETURN
END;