触发器练习

---XS_KC 通过触发器限制"学号"列必须在XS表中存在学号

create  TRIGGER check_trig
        ON XS_KC
        FOR INSERT,update
AS
if exists(SELECT * 
           FROM inserted a
            WHERE a.学号 NOT IN (SELECT b.学号 FROM XS b) 
                OR
             a.课程号 NOT IN (SELECT c.课程号 FROM KC c))
BEGIN
        RAISERROR ('违背数据的一致性.', 16, 1)
        ROLLBACK TRANSACTION
END
go
-----------------------------------------------------------------------------------------------------------------------
---XS_KC 通过触发器限制"学分"列变动自动修改XS表的"总学分"
create  TRIGGER xs_kc_xf_trig
        ON XS_KC
        FOR insert,update,delete 
AS
BEGIN
        update xs set 总学分=总学分+i.学分
                from inserted i
                where xs.学号 = i.学号;

        update xs set 总学分=总学分-d.学分
                from deleted d
                where xs.学号 = d.学号;

END
go

-------------------------------------------------------------------------------------------------
--XS通过触发器级联删除XS_KC
create  TRIGGER xs_cascade_delete_trig
        ON XS
        FOR delete
AS
BEGIN
        delete from xs_kc 
                from deleted d
                where xs_kc.学号=d.学号
END
go
------------------------------------------------------------------------------------------------------------
--XS通过触发器级联修改删除XS_KC
create  TRIGGER xs_cascade_update_trig
        ON XS
        FOR update
AS
BEGIN
if update(学号)
begin
        IF (select COUNT(*) from inserted) <= 1
                 update xs_kc set  xs_kc.学号=i.学号
                        from inserted i, deleted d
                        where xs_kc.学号=d.学号
        else
            begin
                 RAISERROR ('只允许修改一个学号.', 16, 1)
                 ROLLBACK TRANSACTION
            end
        
end
END
GO