I have the following SQL Script: CREATE OR REPLACE TRIGGER SCSP_VIEW_UPDATE INSTEAD OF UPDATE ON SCSP_VIEW FOR EACH ROW BEGIN IF :OLD.StudentFirstName <> :NEW.StudentFirstName THEN UPDATE SECTION SET StudentFirstName = :NEW.StudentFirstName WHERE StudentID = :OLD.StudentID; END IF; IF :OLD.StudentLastName <> :NEW.StudentLastName THEN UPDATE SECTION SET StudentLastName = :NEW.StudentLastName WHERE StudentID = :OLD.StudentID; END IF; IF :OLD.CourseName <> :NEW.CourseName THEN UPDATE SECTION SET CourseName = :NEW.CourseName WHERE CourseID = :OLD.CourseID; END IF; IF :OLD.SectionName <> :NEW.SectionName THEN UPDATE SECTION SET SectionName = :NEW.SectionName WHERE SectionID = :OLD.SectionID; END IF; IF :OLD.ProfessorFirstName <> :NEW.ProfessorFirstName THEN UPDATE SECTION SET ProfessorFirstName = :NEW.ProfessorFirstName WHERE ProfessorID = :OLD.ProfessorID; END IF; IF :OLD.ProfessorLastName <> :NEW.ProfessorLastName THEN UPDATE SECTION SET ProfessorLastName = :NEW.ProfessorLastName WHERE ProfessorID = :OLD.ProfessorID; END IF; END; I am trying to figure out how to add logic so that blanks and empty strings cannot be used to update these columns, and to add a condition that unassigns a professor from a section (UPDATE SectionID) when both the Professor's first and last names are blank or empty strings
I have the following SQL Script:
CREATE OR REPLACE TRIGGER SCSP_VIEW_UPDATE
INSTEAD OF UPDATE ON SCSP_VIEW FOR EACH ROW
BEGIN
IF :OLD.StudentFirstName <> :NEW.StudentFirstName THEN
UPDATE SECTION SET StudentFirstName = :NEW.StudentFirstName
WHERE StudentID = :OLD.StudentID;
END IF;
IF :OLD.StudentLastName <> :NEW.StudentLastName THEN
UPDATE SECTION SET StudentLastName = :NEW.StudentLastName
WHERE StudentID = :OLD.StudentID;
END IF;
IF :OLD.CourseName <> :NEW.CourseName THEN
UPDATE SECTION SET CourseName = :NEW.CourseName
WHERE CourseID = :OLD.CourseID;
END IF;
IF :OLD.SectionName <> :NEW.SectionName THEN
UPDATE SECTION SET SectionName = :NEW.SectionName
WHERE SectionID = :OLD.SectionID;
END IF;
IF :OLD.ProfessorFirstName <> :NEW.ProfessorFirstName THEN
UPDATE SECTION SET ProfessorFirstName = :NEW.ProfessorFirstName
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
IF :OLD.ProfessorLastName <> :NEW.ProfessorLastName THEN
UPDATE SECTION SET ProfessorLastName = :NEW.ProfessorLastName
WHERE ProfessorID = :OLD.ProfessorID;
END IF;
END;
I am trying to figure out how to add logic so that blanks and empty strings cannot be used to update these columns,
and to add a condition that unassigns a professor from a section (UPDATE SectionID) when both the Professor's first and last names are blank or empty strings
Step by step
Solved in 2 steps
