约束、触发器相关

 -- 说明： stu数据库中的student、course、sc三个表的结构同实验一，1-12题在stu数据库中完成；13-17题在student数据库中完成，并验证触发器的执行。18-20题在自己设计的数据库中完成。
-- 以系统管理员身份登录到SQL Server服务器，并使用T-SQL语句实现以下操作：
-- 1.	将stu数据库中student表的sno定义为主键；
USE stu
GO
ALTER TABLE Student
ADD CONSTRAINT stu_pk PRIMARY KEY(Sno);
-- 2.	将数据库stu的表course的cno字段定义为主键，约束名称为cno_pk;
ALTER TABLE Course
ADD CONSTRAINT cno_pk PRIMARY KEY (Cno);
-- 3.	为表course中的字段cname添加唯一值约束；
ALTER TABLE Course
ADD CONSTRAINT course_uni UNIQUE (Cname);
-- 4.	将数据库stu的表sc的sno及cno字段组合定义为主键，约束名称为sc_pk;
ALTER TABLE SC
ADD CONSTRAINT sc_pk PRIMARY KEY (Sno,Cno);
-- 5.	对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：
ALTER TABLE SC
ADD FOREIGN KEY (Sno) REFERENCES Student(Sno);
ALTER TABLE SC
ADD FOREIGN KEY (Cno) REFERENCES Course(Cno);
-- 1)	删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；
ALTER TABLE SC
ADD CONSTRAINT F1 FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE ;
-- 2)	修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；
ALTER TABLE SC
ADD CONSTRAINT F2 FOREIGN KEY (Sno) REFERENCES Student(Sno) ON UPDATE NO ACTION ;
-- 3)	修改course表cno字段值时，该字段在sc表中的对应值也应修改；
ALTER TABLE SC
ADD CONSTRAINT F3 FOREIGN KEY (Cno) REFERENCES Course(Cno) ON UPDATE CASCADE ;
-- 4)	删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；
ALTER TABLE SC
ADD CONSTRAINT F4 FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE CASCADE ;
-- 5)	向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；
ALTER TABLE SC
ADD CONSTRAINT F5 FOREIGN KEY (Sno) REFERENCES Student(Sno);
-- 6.	定义check约束，要求学生学号sno必须为9位数字字符，且不能以0开头，第二三位皆为0；
ALTER TABLE Student
ADD CONSTRAINT stu_sno_check CHECK(Sno LIKE '[1-9]00______');
-- 7.	定义stu数据库中student表中学生年龄值在16-25范围内；
ALTER TABLE Student
ADD CONSTRAINT stu_sage_check CHECK(Sage BETWEEN 16 AND 25);
-- 8.	定义stu数据库中student表中学生姓名长度在2-8之间；
ALTER TABLE Student
ADD CONSTRAINT stu_sname_check CHECK(LEN(Sname) BETWEEN 2 AND 8);
-- 9.	定义stu数据库中student表中学生性别列中只能输入“男”或“女”；
ALTER TABLE Student
ADD CONSTRAINT stu_ssex_check CHECK (Ssex IN ('男','女'));
-- 10.	定义stu数据库student表中学生年龄值默认值为20；
ALTER TABLE Student
ADD CONSTRAINT stu_sage_default DEFAULT 20 FOR Sage;
-- 11.	修改student表学生的年龄值约束可以为15-30范围内；
ALTER TABLE Student
ADD CONSTRAINT stu_sage_check CHECK(Sage BETWEEN 15 AND 30);
-- 12.	删除上述唯一值约束、外键约束及check约束；
ALTER TABLE Course
DROP CONSTRAINT course_uni;
ALTER TABLE SC
DROP CONSTRAINT F1,F2,F3,F4,F5;
ALTER TABLE Student
DROP CONSTRAINT stu_sno_check,stu_sage_check,stu_sname_check,stu_ssex_check,stu_sage_default;
-- 13.	设计触发器实现如果一个学生转专业了，那么输出一条信息显示该学生各门课程的平均分。
CREATE TRIGGER t13 ON Student AFTER UPDATE AS
    IF(UPDATE(Sdept))
        BEGIN
            DECLARE @sno CHAR(9)
            SELECT @sno=Sno FROM inserted
            SELECT AVG(Grade) '平均分' FROM SC WHERE SC.Sno=@sno
        end;
GO
UPDATE Student SET Sdept='MA' WHERE Sno='200515001';
-- 14.	设计触发器实现如果成绩被修改了20分以上，则输出提示信息“修改成绩超过20分，请慎重”。
CREATE TRIGGER t14 ON SC AFTER UPDATE AS
    DECLARE @ograde int,@ngrade int
    SELECT @ograde=Grade FROM deleted
    SELECT @ngrade=Grade FROM inserted
    IF(ABS(@ograde-@ngrade)>20)
        BEGIN
             PRINT '修改成绩超过20分，请慎重'
        end;
    GO
UPDATE SC SET Grade=100 WHERE Sno='200515001' AND Cno='1';
-- 15.在课程表里添加一列：选课人数，设置触发器每门课如果有人选修了那么课程表里的选课人数相应修改，考虑批量插入选课记录的情况。
ALTER TABLE Course
ADD Cselect INT;
CREATE VIEW sc_select
    AS
    SELECT C.Cno,COUNT(*) AS 'amount'
    FROM SC JOIN Course C on C.Cno = SC.Cno
    GROUP BY C.Cno;
UPDATE Course
    SET Course.Cselect=(SELECT amount FROM sc_select WHERE Course.CNO=sc_select.Cno)
    WHERE Course.Cno IN (SELECT Cno FROM sc_select);

CREATE TRIGGER t15 ON SC AFTER INSERT AS
    BEGIN
    DECLARE @cno CHAR(4)

    DECLARE csr CURSOR
    FOR SELECT Cno FROM inserted
    OPEN csr
    FETCH NEXT FROM csr INTO @cno
    WHILE @@FETCH_STATUS=0
        BEGIN
        UPDATE Course SET Cselect=Cselect+1 WHERE Cno=@cno
        FETCH NEXT FROM csr INTO @cno
        end
    DEALLOCATE csr
    END;

GO
--对SC执行的更新，需要SC上的触发器响应，你建course上肯定没反应
--批量修改需要使用游标
INSERT INTO SC(SNO, CNO, GRADE) VALUES
    ('200515001','10',80),
    ('200515002','10',60),
    ('200515003','2',92);

-- 16.设计一触发器，约束每门课程的课容量为120。
CREATE TRIGGER t16 ON SC INSTEAD OF INSERT AS
    BEGIN
    DECLARE @cno CHAR(4),@sno CHAR(9),@grade SMALLINT,@cnt INT

    DECLARE csr2 CURSOR
    FOR SELECT Sno,Cno,Grade FROM inserted
    OPEN csr2
    FETCH NEXT FROM csr2 INTO @sno,@cno,@grade
    WHILE @@FETCH_STATUS=0
        BEGIN
        SELECT @cnt=Cselect FROM Course
        IF(@cnt<=120)
            BEGIN
                INSERT INTO SC(Sno, Cno, Grade)
                VALUES (@sno,@cno,@grade)
            end
        ELSE
            PRINT '课容量已满！'
        FETCH NEXT FROM csr2 INTO @sno,@cno,@grade
        end
    DEALLOCATE csr2
    END;

GO
INSERT INTO SC(SNO, CNO, GRADE) VALUES
    ('200515003','10',80),
    ('200515021','10',60);
-- 17.如果一门课的学分发生了修改， 则所有选修该课程并且及格的学生的总学分要做相应的修改，并输出学号，姓名，原总学分，新总学分
ALTER TABLE Student
ADD Scredit SMALLINT DEFAULT 0;
CREATE VIEW stu_credit
    AS
    SELECT Student.Sno,SUM(Ccredit) AS 'Scredit'
    FROM Student JOIN dbo.SC S on Student.Sno = S.Sno JOIN Course C2 on S.Cno = C2.Cno
    WHERE S.Grade>=60
    GROUP BY Student.Sno;
UPDATE Student
    SET Student.Scredit=(SELECT Scredit FROM stu_credit WHERE Student.Sno=stu_credit.Sno)
    WHERE Student.Sno IN (SELECT Sno FROM stu_credit);

CREATE TRIGGER t17 ON Course AFTER UPDATE AS
    IF(UPDATE(Ccredit))
        BEGIN
            DECLARE @cno CHAR(4),@ocredit SMALLINT,@ncredit SMALLINT
            SELECT @ocredit=Ccredit FROM deleted
            SELECT @cno=Cno,@ncredit=Ccredit FROM inserted

            UPDATE Student
            SET Student.Scredit=Student.Scredit-@ocredit+@ncredit
            WHERE Student.Sno IN(SELECT Sno FROM SC WHERE Cno=@cno AND Grade>=60)
    
            SELECT Sno,Sname,Scredit-@ncredit+@ocredit AS '原总学分',Scredit AS '新总学分'
            FROM Student
            WHERE  Student.Sno IN(SELECT Sno FROM SC WHERE Cno=@cno AND Grade>=60)
        end;
    GO

UPDATE Course SET Ccredit=1 WHERE Cno='8';

-- 18．设有两个表：商品库存表（商品编号，商品名称，库存数量，库存单价，库存金额）；
-- 商品销售表（商品编号，商品名称，购货商号，销售数量，销售单价，销售金额）；设计一触发器实现如下业务规则：
--新建数据库Commodity
USE Commodity
GO
CREATE TABLE Storage
(
    PNO CHAR(9) PRIMARY KEY ,
    PNAME VARCHAR(20),
    PAMOUNT INT,
    PPRICE MONEY,
    P_sum_price MONEY
)
CREATE TABLE Sales
(
    PNO CHAR(9) PRIMARY KEY ,
    PNAME VARCHAR(20),
    PURCHASE_ID CHAR(9),
    SAMOUNT INT,
    SPRICE MONEY,
    S_sum_price MONEY,
    FOREIGN KEY(PNO) REFERENCES Storage(PNO)
)
GO
-- （1）保证在商品库存表中插入的数据，库存金额 = 库存数量 * 库存单价。
CREATE TRIGGER t18 ON Storage AFTER INSERT,UPDATE AS
    UPDATE Storage SET P_sum_price=Storage.PAMOUNT*PPRICE
    WHERE PNO IN(SELECT PNO FROM inserted);
GO
INSERT INTO Storage(PNO, PNAME, PAMOUNT, PPRICE)VALUES
('2008','芝士',10,66),
('1002','洗衣粉',20,39),
('1001','面包',30,10);
-- （2）如果销售的商品不存在库存或者库存为零，则返回提示信息。否则自动减少商品库存表中对应商品的库存数量和库存金额。
CREATE TRIGGER t18_2 ON Sales AFTER INSERT,UPDATE AS
    UPDATE Sales SET S_sum_price=SAMOUNT*SPRICE
    WHERE PNO IN(SELECT PNO FROM inserted);

CREATE TRIGGER t18_3 ON Sales INSTEAD OF INSERT AS
    DECLARE @pno CHAR(9),@pname VARCHAR(20),@purchase CHAR(9),@amount INT,@price MONEY
    SELECT @pno=PNO,@pname=PNAME,@purchase=PURCHASE_ID,@amount=SAMOUNT,@price=SPRICE FROM inserted

    DECLARE csr3 CURSOR FOR
    SELECT PNO,PNAME,PURCHASE_ID,SAMOUNT,SPRICE FROM inserted
    OPEN csr3
    FETCH NEXT FROM csr3 INTO @pno,@pname,@purchase,@amount,@price
    
    WHILE @@FETCH_STATUS=0
    BEGIN
        IF NOT EXISTS(SELECT * FROM Storage WHERE @pno=PNO)
            PRINT'商品不存在！'
        ELSE IF @amount>(SELECT PAMOUNT FROM Storage WHERE @pno=PNO)
            PRINT'商品库存不足！'
        ELSE
        BEGIN
            UPDATE Storage SET PAMOUNT=PAMOUNT-@amount WHERE @pno=PNO
            INSERT INTO Sales(PNO, PNAME, PURCHASE_ID, SAMOUNT, SPRICE, S_sum_price)
            VALUES (@pno,@pname,@purchase,@amount,@price,@amount*@price)
        end
        FETCH NEXT FROM csr3 INTO @pno,@pname,@purchase,@amount,@price
    end
    GO

INSERT INTO Sales(PNO, PNAME, PURCHASE_ID, SAMOUNT, SPRICE) VALUES
('2008','芝士','11',11,100),
('1001','面包','10',15,15);
-- 建表并验证触发器的执行。
-- 19 ．建立教师表（教工编号，姓名，专业，职称，工资）和
-- 工资变化表（教工编号，原工资，新工资），
-- 设计触发器实现教授的工资不得低于4000元，如果低于4000元则自动改为4000元。
USE Teacher
GO
CREATE TABLE Teacher
(
    TNO CHAR(9) PRIMARY KEY ,
    TNAME VARCHAR(20),
    TDEPT CHAR(4),
    TTITLE VARCHAR(10),
    TSALARY MONEY
)
CREATE TABLE Salary
(
    TNO CHAR(9) PRIMARY KEY ,
    T_old_salary MONEY,
    T_new_salary MONEY,
    FOREIGN KEY (TNO) REFERENCES Teacher(TNO)
)
CREATE TRIGGER t19 ON Teacher AFTER INSERT,UPDATE AS
    DECLARE @salary MONEY,@title VARCHAR(10),@tno CHAR(9)
    SELECT @salary=TSALARY,@title=TTITLE,@tno=TNO FROM inserted
    IF(@title='Professor' AND @salary<4000)
        BEGIN
            PRINT'教授工资不得低于4000'
            UPDATE Teacher SET TSALARY=4000 WHERE @tno=TNO
        end;
    GO
INSERT INTO Teacher(TNO, TNAME, TDEPT, TTITLE, TSALARY)
VALUES ('202115001','张老师','CS','Professor',3900);

-- 20.	使用第19题的两个表设计触发器实现如果教工的工资发生变化则向工资变化表插入一条记录，包含教工编号，原工资，新工资。
CREATE TRIGGER t20 ON Teacher AFTER UPDATE AS
    IF(UPDATE(TSALARY))
        BEGIN
            DECLARE @osalary MONEY,@nsalary MONEY,@tno CHAR(9)
            SELECT @osalary=TSALARY FROM deleted
            SELECT @nsalary=TSALARY,@tno=TNO FROM inserted

            INSERT INTO Salary(TNO, T_old_salary, T_new_salary)
            VALUES(@tno,@osalary,@nsalary)
        end;
    GO

UPDATE Teacher SET TSALARY=100000 WHERE TNO='202115001';

-- 21. 针对northwind数据库实现触发器：每个员工每天处理订单的数量不能超过100，如果超出100个则拒绝处理，并提示“处理订单数量超出限额”
USE Northwind
GO
CREATE TRIGGER t21 ON Orders AFTER INSERT AS
    BEGIN TRANSACTION
        DECLARE @orders INT,@id INT,@date DATETIME
        SELECT @id=EmployeeID,@date=OrderDate FROM inserted
        SELECT @orders=COUNT(*)
        FROM Orders
        WHERE @id=EmployeeID AND @date=OrderDate
        GROUP BY EmployeeID
        IF(@orders>=100)--测试时候实际用的1，因为每天的订单库中比较少
        BEGIN
            PRINT'Maximum number of transactions reached(max 100 per day)'
            ROLLBACK
        end;
GO

SET IDENTITY_INSERT Orders ON
INSERT INTO Orders (OrderID,CustomerID,EmployeeID,OrderDate)
VALUES ('10002','VINET',4,'1996-07-04 00:00:00.000')

-- 22.针对northwind数据库实现触发器：给orders表添加zje（订单总金额）属性列，
-- 要求保留两位小数，设置触发器实现当产生了新的订单明细之后将总金额更新到到订单表里
ALTER TABLE Orders
ADD zje MONEY DEFAULT 0;

CREATE TRIGGER t22 ON [Order Details] AFTER INSERT,UPDATE AS
    DECLARE @oid INT,@pid INT,@price MONEY,@quan SMALLINT,@dis REAL,@sum MONEY
    SELECT @oid=OrderID,@pid=ProductID,@price=UnitPrice,@quan=Quantity,@dis=Discount
    FROM inserted
    SELECT @sum=SUM(UnitPrice*Quantity*(1-Discount)) FROM [Order Details] WHERE @oid=OrderID GROUP BY OrderID
    UPDATE Orders SET Orders.zje=@sum WHERE @oid=OrderID
    GO

INSERT INTO [Order Details](OrderID, ProductID, UnitPrice, Quantity, Discount)
VALUES (10001,6,20,10,0);

-- -- 一些想法
-- 主要是针对实验中存在批量修改的题目，如果使用update语句批量更新，就可以一次性完成对多条语句的修改
-- 但是如果使用DECLARE定义变量然后再进行修改，通过SELECT获得的inserted表中的值就只能是第一个元组中的值，
-- 从而没办法批量更新；
-- 这个时候就必须要用到游标吗？（我是上网查到用变量+游标实现批量更新的）
-- （然后我就一部分题目，题目要求明确需要实现批量更新的就写了游标的...）