SSMS(SQL SERVER)之触发器TRIGGER

找到一篇讲触发器的好文章：

http://t.csdnimg.cn/wnR6W

吐槽：触发器的创建没问题，但是激发之后要查看“结果”选项卡的时候这东西不见了！！！

不是不出来，SELECT的时候显示完全没问题，但是就是触发器应该被触发的时候只有“命令已成功完成”。。。。。。真的很想报警！

记录一下，万一是自己哪里写错了呢......

1.先创建三个基本表：Student Course SC

--/*
USE lab6		-- 转到lab6数据库
GO

--每次重新创建表，语句可以重复执行
DROP TABLE if exists SC;
DROP TABLE if exists Student;
DROP TABLE if exists Course;

--Student:
CREATE TABLE  Student(
Sno CHAR(12) PRIMARY KEY NOT NULL, 
Sname CHAR(20) UNIQUE ,
Ssex CHAR(2) ,
Sage SMALLINT ,
Sdept CHAR(20) 
); 
--1.Sno
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'学生编号，主键',
	N'user',
	N'dbo',
	N'table',
	N'Student',
	N'column',
	N'Sno'
--2.Sname
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'姓名',
	N'user',
	N'dbo',
	N'table',
	N'Student',
	N'column',
	N'Sname'
--3.Ssex
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'性别',
	N'user',
	N'dbo',
	N'table',
	N'Student',
	N'column',
	N'Ssex'
--4.Sage
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'年龄',
	N'user',
	N'dbo',
	N'table',
	N'Student',
	N'column',
	N'Sage'
--5.Sdept
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'系别',
	N'user',
	N'dbo',
	N'table',
	N'Student',
	N'column',
	N'Sdept'
--Course：
CREATE TABLE  Course(
Cno CHAR(4) PRIMARY KEY  NOT NULL,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT ,
FOREIGN KEY(Cpno) REFERENCES Course(Cno)
); 
--1.Cno
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'课程标号，主键',
	N'user',
	N'dbo',
	N'table',
	N'Course',
	N'column',
	N'Cno'
--2.Cname
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'课程名',
	N'user',
	N'dbo',
	N'table',
	N'Course',
	N'column',
	N'Cname'
--3.Cpno
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'先修课程名，外键',
	N'user',
	N'dbo',
	N'table',
	N'Course',
	N'column',
	N'Cpno'
--4.Ccredit
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'学分',
	N'user',
	N'dbo',
	N'table',
	N'Course',
	N'column',
	N'Ccredit'

--SC：
CREATE TABLE  SC(
Sno CHAR(12)  NOT NULL,
Cno CHAR(4)  NOT NULL,
Grade SMALLINT ,
PRIMARY KEY(Sno,Cno),
--FOREIGN KEY(Cno) REFERENCES  Course(Cno),
--FOREIGN KEY(Sno) REFERENCES  Student(Sno)
); 
--1.Sno
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'学生编号，外键',
	N'user',
	N'dbo',
	N'table',
	N'SC',
	N'column',
	N'Sno'
--2.Cno
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'课程标号，外键',
	N'user',
	N'dbo',
	N'table',
	N'SC',
	N'column',
	N'Cno'
--3.Grade
EXECUTE sp_addextendedproperty 
	N'MS_Description',
	'成绩',
	N'user',
	N'dbo',
	N'table',
	N'SC',
	N'column',
	N'Grade'

--1.插入表格信息（行）：
--Students：
insert into Student(Sno,Sname,Ssex,Sage,Sdept) 
values('121022022046','陈雪蕊','女',19,'JK')
insert into Student(Sno,Sname,Ssex,Sage,Sdept) 
values('201215121','李勇','男',20,'CS')
insert into Student(Sno,Sname,Ssex,Sage,Sdept) 
values('201215122','刘晨','女',19,'CS')
insert into Student(Sno,Sname,Ssex,Sage,Sdept) 
values('201215123','王敏','女',18,'MA')
insert into Student(Sno,Sname,Ssex,Sage,Sdept) 
values('201215125','张立','男',19,'IS')


--Course:
insert into Course(Cno,Cname,Cpno,Ccredit)
values('6','数据处理',NULL,2)
insert into Course(Cno,Cname,Cpno,Ccredit)
values('2','数学',NULL,2)
insert into Course(Cno,Cname,Cpno,Ccredit)
values('4','操作系统','6',3)
insert into Course(Cno,Cname,Cpno,Ccredit)
values('7','PASCAL语言','6',4)
insert into Course(Cno,Cname,Cpno,Ccredit)
values('5','数据结构','7',4)
insert into Course(Cno,Cname,Cpno,Ccredit)
values('1','数据库','5',4)
insert into Course(Cno,Cname,Cpno,Ccredit)
values('3','信息系统','1',4)


--SC:
insert into SC(Sno,Cno,Grade)
values('201215121','1',92)
insert into SC(Sno,Cno,Grade)
values('201215121','2',85)
insert into SC(Sno,Cno,Grade)
values('201215121','3',88)
insert into SC(Sno,Cno,Grade)
values('201215122','2',90)
insert into SC(Sno,Cno,Grade)
values('201215122','3',80)

--SELECT Sno,Sname FROM Student;

2.触发器的创建：

USE lab6		-- 转到lab6数据库
GO
--删除触发器
DROP TRIGGER if EXISTS tri_UPDATE_DELETE_sc;

USE lab6		-- 转到lab6数据库
GO
--创建触发器
CREATE TRIGGER tri_UPDATE_DELETE_sc
ON SC
FOR UPDATE,DELETE
--BEFORE UPDATE|DELETE ON SC
AS
--检测成绩列表是否被更新
IF UPDATE(Grade)
BEGIN
--显示学号、课程号、原成绩和新成绩信息
SELECT INSERTED.Cno,DELETED.Grade AS Grade,INSERTED.Grade AS NewGrade
FROM DELETED ,INSERTED
WHERE DELETED.Sno = INSERTED.Sno
END

--检测是更新还是删除操作
ELSE IF COLUMNS_UPDATED( )=0
BEGIN
--显示被删除的学号、课程号和成绩信号
SELECT DELETED.Sno ,DELETED.Cno ,DELETED.Grade AS Grade
FROM DELETED
END
ELSE
--返回提示信息
PRINT '更新了非成绩列！';

3.激发触发器的几条例句：

--2.1
UPDATE SC
SET Grade = Grade+5
WHERE Cno='2';

--/*
--2.2
UPDATE SC
SET Cno = '113'
WHERE Cno='1'

--2.3
DELETE SC
WHERE Cno='113'

--3.1
ALTER TABLE SC
ADD CONSTRAINT CK_Grade
CHECK(Grade>=0 AND Grade<=100)

--3.2
UPDATE SC
SET Grade=120
WHERE Cno='1'

--3.3
UPDATE SC
SET Grade=90
WHERE Cno='1'
--*/

4.不合理的结果（同学的机子上运行没问题的...）：

接下来是自己写代码的2道题：

1.在Student表中编写insert的触发器，假如每个班的学生不能超过30个，如果低于此数，添加可以完成；如果超过此数，则插入将不能实现。

DROP TRIGGER IF EXISTS tri_UPDATE_INSERT_stu;
USE lab6
GO
CREATE TRIGGER tri_UPDATE_INSERT_stu
ON Student
INSTEAD OF INSERT  --执行前触发
NOT FOR REPLICATION --告诉DMBMS，当复制表时，触发器不能被执行
AS
declare @sno numeric
SELECT @sno=Sno from Student

IF(@sno>30)
BEGIN
ROLLBACK --撤回操作
print'插入不成功！'
END

ELSE
print'插入成功！'

2.在SC表上编写update触发器，当修改SC表中的grade字段时将其修改前后的信息保存在SC_log表中。

--/*
--2.1先创建SC_log表：
DROP TABLE IF EXISTS SC_log;
USE lab6
GO
CREATE TABLE SC_log(
Sno CHAR(12)  NOT NULL,
Cno CHAR(4)  NOT NULL,
Grade SMALLINT ,
); 
--*/
--/*
--2.2在SC表上编写update触发器：
DROP TRIGGER IF EXISTS tri_UPDATE_SC
USE lab6
GO
CREATE TRIGGER tri_UPDATE_SC
ON SC
FOR UPDATE
AS
IF UPDATE(Grade)
BEGIN
INSERT INTO SC_log SELECT * FROM DELETED
INSERT INTO SC_log SELECT * FROM INSERTED
PRINT'修改Grade列...'

END
ELSE
PRINT'修改其他列...'
--测试trigger:
insert into SC(Sno,Cno,Grade)
values('201215121','3',88)
--*/

UPDATE SC
SET SC.Grade=100
WHERE SC.Cno='3' AND SC.Sno='201215121'