SQLize Online / PHPize Online  /  SQLtest Online

-- ON DELETE CASCADE Instructor CREATE OR ALTER TRIGGER Delete_Instructor ON Instructor INSTEAD OF DELETE AS DECLARE @t TABLE (Ins_ID INT) INSERT INTO @t SELECT Ins_ID FROM Deleted UPDATE E SET Ins_ID = NULL FROM Exam E JOIN @t T ON E.Ins_ID = T.Ins_ID DELETE CI FROM Crs_Ins CI JOIN @t T ON CI.Ins_ID = T.Ins_ID; DELETE QI FROM Quest_Ins QI JOIN @t T ON QI.Ins_ID = T.Ins_ID; DELETE I FROM Instructor I JOIN @t T ON I.Ins_ID = T.Ins_ID; GO -- ON DELETE CASCADE Student CREATE OR ALTER TRIGGER Delete_Student ON Student INSTEAD OF DELETE AS DECLARE @t TABLE (St_ID INT) INSERT INTO @t SELECT St_ID FROM Deleted DELETE SE FROM St_Ex SE JOIN @t T ON SE.St_ID = T.St_ID; DELETE SC FROM St_Crs SC JOIN @t T ON SC.St_ID = T.St_ID; DELETE SM FROM St_Mgr SM JOIN @t T ON SM.St_ID = T.St_ID; DELETE SB FROM St_Br SB JOIN @t T ON SB.St_ID = T.St_ID; DELETE SEQ FROM St_Ex_Quest SEQ JOIN @t T ON SEQ.St_ID = T.St_ID; DELETE S FROM Student S JOIN @t t ON S.St_ID = t.St_ID; GO -- ON DELETE CASCADE Course CREATE OR ALTER TRIGGER Delete_Course ON Course INSTEAD OF DELETE AS DECLARE @t TABLE (Crs_ID INT) INSERT INTO @t SELECT Crs_ID FROM Deleted DELETE QP FROM Question_Pool QP JOIN @t T ON QP.Crs_ID = T.Crs_ID; DELETE E FROM Exam E JOIN @t T ON E.Crs_ID = T.Crs_ID; DELETE SC FROM St_Crs SC JOIN @t T ON SC.Crs_ID = T.Crs_ID; DELETE CI FROM Crs_Ins CI JOIN @t T ON CI.Crs_ID = T.Crs_ID; DELETE C FROM Course C JOIN @t T ON C.Crs_ID = T.Crs_ID; GO -- ON DELETE CASCADE Question_Pool CREATE OR ALTER TRIGGER Delete_Question_Pool ON Question_Pool INSTEAD OF DELETE AS DECLARE @t TABLE (Quest_ID INT) INSERT INTO @t SELECT Quest_ID FROM Deleted DELETE QO FROM Quest_Options QO JOIN @t T ON QO.Quest_ID = T.Quest_ID; DELETE QI FROM Quest_Ins QI JOIN @t T ON QI.Quest_ID = T.Quest_ID; DELETE QE FROM Quest_Ex QE JOIN @t T ON QE.Quest_ID = T.Quest_ID; DELETE SEQ FROM St_Ex_Quest SEQ JOIN @t T ON SEQ.Quest_ID = T.Quest_ID; DELETE QP FROM Question_Pool QP JOIN @t T ON QP.Quest_ID = T.Quest_ID; GO -- ON DELETE CASCADE Exam CREATE OR ALTER TRIGGER Delete_Exam ON Exam INSTEAD OF DELETE AS DECLARE @t TABLE (Ex_ID INT) INSERT INTO @t SELECT Ex_ID FROM Deleted DELETE SE FROM St_Ex SE JOIN @t T ON SE.Ex_ID = T.Ex_ID; DELETE QE FROM Quest_Ex QE JOIN @t T ON QE.Ex_ID = T.Ex_ID; DELETE SE FROM St_Ex_Quest SE JOIN @t T ON SE.Ex_ID = T.Ex_ID; DELETE E FROM Exam E JOIN @t T ON E.Ex_ID = T.Ex_ID; GO -- ON DELETE CASCADE Training_Manager CREATE OR ALTER TRIGGER Delete_Training_Manager ON Training_Manager INSTEAD OF DELETE AS DECLARE @t TABLE (TM_ID INT) INSERT INTO @t SELECT TM_ID FROM Deleted UPDATE B SET B.TM_ID = NULL FROM Branch B JOIN @T T ON B.TM_ID = T.TM_ID; DELETE SM FROM St_Mgr SM JOIN @t T ON SM.TM_ID = T.TM_ID; DELETE TM FROM Training_Manager TM JOIN @t T ON TM.TM_ID = T.TM_ID; GO -- ON DELETE CASCADE Branch CREATE OR ALTER TRIGGER Delete_Branch ON Branch INSTEAD OF DELETE AS DECLARE @t TABLE (Br_ID INT) INSERT INTO @t SELECT Br_ID FROM Deleted DELETE I FROM Intake I JOIN @t T ON I.Br_ID = T.Br_ID; DELETE SB FROM St_Br SB JOIN @t T ON SB.Br_ID = T.Br_ID; DELETE B FROM Branch B JOIN @t T ON B.Br_ID = T.Br_ID; GO -- ON DELETE CASCADE Intake CREATE OR ALTER TRIGGER Delete_Intake ON Intake INSTEAD OF DELETE AS DECLARE @t TABLE (In_ID INT) INSERT INTO @t SELECT In_ID FROM Deleted DELETE Tr FROM Track Tr JOIN @t T ON Tr.In_ID = T.In_ID; DELETE IT FROM Intake IT JOIN @t T ON IT.In_ID = T.In_ID; GO -- ON DELETE CASCADE Quest_Ex CREATE OR ALTER TRIGGER Delete_Quest_Ex ON Quest_Ex INSTEAD OF DELETE AS DECLARE @t TABLE (Quest_ID INT, Ex_ID INT) INSERT INTO @t SELECT * FROM Deleted DELETE SEQ FROM St_Ex_Quest SEQ JOIN @t T ON T.Ex_ID = SEQ.Ex_ID AND T.Quest_ID = SEQ.Quest_ID DELETE QE FROM Quest_Ex QE JOIN @t T ON T.Ex_ID = QE.EX_ID AND T.Quest_ID = QE.Quest_ID GO -- ON UPDATE CASCADE Quest_Ex CREATE OR ALTER TRIGGER Update_Quest_Ex ON Quest_Ex INSTEAD OF UPDATE AS DECLARE @old_t TABLE (Quest_ID INT, Ex_ID INT) INSERT INTO @old_t SELECT * FROM Deleted DECLARE @new_t TABLE (Quest_ID INT, Ex_ID INT) INSERT INTO @new_t SELECT * FROM Inserted DELETE SEQ FROM St_Ex_Quest SEQ JOIN @old_t OT ON OT.Ex_ID = SEQ.Ex_ID AND OT.Quest_ID = SEQ.Quest_ID DELETE QE FROM Quest_Ex QE JOIN @old_t OT ON OT.Ex_ID = QE.EX_ID AND OT.Quest_ID = QE.Quest_ID INSERT INTO Quest_Ex SELECT * FROM @new_t INSERT INTO St_Ex_Quest (Quest_ID, Ex_ID) SELECT * FROM @new_t