触发器

Create Triggers

建立学生表的触发器，当增加学生时，学生不能超过25岁，如超过给出提示

– 插入你本人的记录，验证执行情况

出现警告

对表 Course 建触发器，实现级联删除的功能，但某课选修人数大于3的课程不删除

– 提示：先删除 SC 表对Course的引用，验证触发器执行情况

Create TRIGGER COURSE_AFT ON  COURSE
 AFTER DELETE
AS
BEGIN
    SET NOCOUNT ON;
    delete from TSC where Cno in
    (
        select Cno from deleted
    )
    AND
    Cno IN
    (
       SELECT Cno
       FROM TSC
       GROUP BY Cno
       HAVING COUNT(Sno)<3
    )
 
END

选课人数少的C006被删除。

在SC表创建触发器，使信息管理系（IS）学生不能选修“数据库原理”这门课程

由于选修“数据库原理”课程的同学少于三人，所以已被删除，这里用“数据结构”课程来进行验证。

CREATE TRIGGER TSC_AFT
ON TSC AFTER INSERT,update
AS
IF EXISTS(
    SELECT *
    FROM Student join inserted on Student.Sno=inserted.Sno
    join Course on inserted.Cno=Course.Cno
    where Sdept='信息管理系' and Cname='数据结构'
)
ROLLBACK

– 用你本人的信息对触发器执行情况进行验证

因为本人不是信息管理系，所以这里以信息管理系的张立代替

insert into TSC values('0821101','c007','88')

引发触发器操作

上述操作能否用完整性约束实现？

不能用完整性约束实现，因为被约束的列位于两个不同表中，check（列取值范围）约束只能约束位于同一表上的列之间的取值约束。同理，其他的完整性约束例如：非空约束、主键约束、外键约束、唯一值约束、默认值约束均不能实现以上操作。

Inserted / Updated

新增一日志（S_History）表（表结构与Student表相同当Student表被插入/删除一行时，把变更记录到日志表中

新建日志表

CREATE TABLE S_History
(   sno varchar（20） primary key,
    sname nchar(5),
    ssex nchar(1),
    sage tinyint,
    sdept nchar(20),
)

Sno应为varchar（20）

在student表上建立插入时的触发器

CREATE TRIGGER S_HISTOR_INSERT_AFT
ON STUDENT AFTER INSERT
AS
BEGIN
    INSERT INTO S_History
    SELECT *
    FROM inserted
 
END

在student表上建立删除时的触发器

CREATE TRIGGER S_HISTOR_DELETE_AFT
ON STUDENT AFTER DELETE
AS
BEGIN
    INSERT INTO S_History
    SELECT *
    FROM deleted
 
END

从Student表中插入/删除你本人的记录

 – 检查该记录是否记入日志（S_History）表

插入本人记录

这里的学号是虚构的

insert into Student values('1508100234','丁兆元','男','21',NULL)

删除本人记录

DELETE FROM Student
WHERE Sno='1508100234'

日志中的记录

INSTEAD OF

建立 S_SC_Avg 视图，并创建替代触发器 S_SC_Avg_Del

 – 删除视图中某学生及平均成绩记录时，删除SC选课表中该学生的

 所有选课记录

视图：

CREATE VIEW S_SC_AVG
AS
SELECT Sno,AVG(GRADE) AVG
FROM TSC
GROUP BY Sno

 

创建触发器

CREATE TRIGGER S_SC_AVG_DEL
ON S_SC_AVG INSTEAD OF DELETE
AS
DELETE FROM TSC
WHERE Sno IN(SELECT Sno FROM DELETED);

通过视图删除本人的平均成绩，观察SC中的记录变化情况
 

DELETE FROM S_SC_AVG
WHERE Sno='1508100201'

原来关于1508100201的两条选课记录因为替代触发器的缘故而被删除。

Computed Fields

建立发票表Bill，并编写触发器

CREATE TABLE bill(
  bill_id int NOT NULL,
  data date ,
  product varchar(20) ,
  price decimal(10,0) ,
  num int ,
  charge decimal(10,0) ,
  PRIMARY KEY (bill_id)
)

实现当输入单价和数量后，自动填写金额，即发票金额不输入，由单价、数量相乘后自动填写到金额中

CREATE TRIGGER tri_bill
 ON bill AFTER INSERT
 AS
    update bill
    SET charge= inserted.num * inserted.price
    from inserted

INSERT INTO bill
VALUES(001, NULL, 't', 25, 4, NULL);

插入单价与数量后总金额成功自动生成。