HBNU-School/数据库/头歌/触发器的创建和使用/1. 触发器的创建和使用.sh
2024-11-19 16:44:10 +08:00

71 lines
1.5 KiB
Bash
Raw Permalink Blame History

This file contains invisible Unicode characters

This file contains invisible Unicode characters that are indistinguishable to humans but may be processed differently by a computer. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.

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