USE studentdb
go

SET NOCOUNT ON 
go
--********** create trigger_insert_score  **********--
--********** Begin **********--
CREATE TRIGGER trigger_insert_score  
ON score  
INSTEAD OF INSERT  
AS  
BEGIN  
    IF EXISTS (SELECT * FROM inserted i WHERE NOT EXISTS (SELECT 1 FROM student s WHERE s.sno = i.sno))  
    BEGIN  
        RETURN  
    END  
    INSERT INTO score (sno, cno, grade)  
    SELECT sno, cno, grade FROM inserted  
END  
--********** End **********--
go
delete from score
go
insert into score values('1001','2001','89.5')
go
insert into score values('1002','2001','95')
go
insert into score values('1011','2001','88')
go
select * from score
go


--********** create trigger_delete_student  **********--
--********** Begin **********--
CREATE TRIGGER trigger_delete_student  
ON student  
INSTEAD OF DELETE  
AS  
BEGIN  
    DELETE FROM score WHERE sno IN (SELECT sno FROM deleted)  
    DELETE FROM student WHERE sno IN (SELECT sno FROM deleted)  
END  
--********** End **********--
go
delete from student where sno='1001'
go
select * from student
go

--********** create trigger_protect_grade **********--
--********** Begin **********--
CREATE TRIGGER trigger_protect_grade  
ON score  
FOR UPDATE  
AS  
BEGIN  
    IF UPDATE(grade)  
    BEGIN  
        ROLLBACK TRANSACTION  
    END  
END  
--********** End **********--
go
delete from score where sno='1001'
go
select * from score
go
select * from student
go