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);
插入单价与数量后总金额成功自动生成。