Sql Server数据库触发器

【实验目的】

1、了解触发器的原理

2、熟悉触发器的使用方法

3、掌握触发器的创建和管理

【实验设备及器材】

1、硬件：PC机；

2、软件：(1)Windows7; (2)Microsoft SQL Server 2012。

【主要内容】

创建触发器；修改和删除触发器；禁用和启用触发器。

【实验内容及要求】/【实验步骤及结果】

下面所有的操作在“学生管理数据库”中进行。（附加数据库，T-SQL语句）

一、创建触发器

为成绩表创建一个基于UPDATE操作和DELETE操作的复合型触发器，当修改了该表中的成绩信息或者删除了成绩记录时，触发器激活生效，显示相关的操作信息。

USE 学生管理数据库

GO

CREATE TRIGGER UP_DEL

ON 成绩信息表

FOR UPDATE,DELETE

AS

BEGIN

DECLARE @学号_COUNT CHAR(10),@课程号_COUNT CHAR(10),@成绩_COUNT TINYINT,@学号_UPDATE CHAR(10),@课程号_UPDATE CHAR(10),@成绩_UPDATE TINYINT

--检测“成绩信息表”是否删除了成绩记录

IF COLUMNS_UPDATED()=0

BEGIN 

--显示被删除记录的学号、课程号、成绩的信息

SELECT @学号_COUNT=DELETED.学号 ,@课程号_COUNT=DELETED.课程号,@成绩_COUNT=DELETED.成绩

FROM DELETED

PRINT '被删除记录的学号：'+@学号_COUNT+'  课程号：'+@课程号_COUNT+'  成绩：'+STR(@成绩_COUNT)

END

ELSE

--检测“成绩信息表”的“成绩”列是否被更新

IF UPDATE(成绩)

    BEGIN

--显示学号、课程号、原成绩和新成绩的信息

SELECT @学号_COUNT=DELETED.学号 ,@课程号_COUNT=DELETED.课程号,@成绩_COUNT=DELETED.成绩

FROM DELETED

SELECT @学号_UPDATE=INSERTED.学号 ,@课程号_UPDATE=INSERTED.课程号,@成绩_UPDATE=INSERTED.成绩

FROM INSERTED

PRINT '学号：'+@学号_COUNT+'  课程号：'+@课程号_COUNT+'  原成绩：'+STR(@成绩_COUNT)+'  新成绩：'++STR(@成绩_UPDATE)

END

ELSE

--返回提示信息

PRINT '更新了非成绩列！'

END

执行效果图：

二、触发触发器

1.执行以下UPDATE语句，修改成绩列，激发触发器。

UPDATE 成绩信息表

SET 成绩=成绩+5

WHERE 学号='14101' AND 课程号='101'

运行结果如下：

2.执行以下UPDATE语句,修改非成绩列，激发触发器。

UPDATE 成绩信息表

SET 课程号='105'

WHERE 学号='14101' AND 课程号='102'

运行结果如下：

3. 执行以下DELETE 成绩表 语句，删除成绩记录，激发触发器。

DELETE 成绩信息表

WHERE 学号='14104' AND 课程号='105'

三、比较约束与触发器的不同作用期

1.执行以下UPDATE语句,修改成绩列，是否激发触发器，或违反了约束，为什么？

UPDATE 成绩信息表

SET 成绩=120

WHERE 学号='14101' AND 课程号='101'

运行图如下：

答：违反了所设置的CHECK约束，在约束中成绩信息表中的成绩要求大于大于0并小于等于100，但是在执行UPDATE操作时却将成绩变为120，120大于所约束的范围，即违反了约束，无法对其进行更新。

2.执行以下CREATE TRIGGERHE和UPDATE语句,修改成绩列，是否激发触发器，或违反了约束，为什么？

USE 学生管理数据库

GO

CREATE TRIGGER TRI_SC_GRADE

ON 成绩信息表

INSTEAD OF UPDATE

AS

IF UPDATE(成绩)

BEGIN

DECLARE @GRADE_NEW TINYINT

SELECT @GRADE_NEW=INSERTED.成绩

FROM INSERTED

IF @GRADE_NEW<0 OR @GRADE_NEW>100

PRINT '新成绩为'++STR(@GRADE_NEW)+'，而成绩只能在0-100之间！'

END

GO

UPDATE 成绩信息表

SET 成绩=120

WHERE 学号='14101' AND 课程号='101'

运行图如下：

答：触发器成功触法，但未违反约束。

因为从触发器类型来说TRI_SC_GRADE触发器属于INSTEAD触发器，而当INSTEAD触发器触发时，则不再会执行UPDATE语句，而是执行触发器里定义的语句，所以成绩值并未被修改为120，因此不存在违反约束的情况。

• 删除成绩信息表上的触发器

所需代码如下：

USE 学生管理数据库

GO

DROP TRIGGER TRI_SC_GRADE

DROP TRIGGER UP_DEL

执行效果图：

五、设计实验

利用触发器，实现“学生信息表”和“成绩信息表”的级联删除和级联更新,也就是，当删除或者修改“学生信息表”的信息时，自动删除或者修改“成绩信息表”的相关信息。

1. 创建触发器

USE 学生管理数据库

GO

CREATE TRIGGER UP_DEL

ON 学生信息表

AFTER UPDATE,DELETE

AS

   IF UPDATE(学号)

      BEGIN

    UPDATE 成绩信息表

    SET 成绩信息表.学号=INSERTED.学号

    FROM 成绩信息表 join INSERTED ON 成绩信息表.学号=INSERTED.学号

      END

   ELSE

      BEGIN

         DELETE FROM 成绩信息表

         WHERE 成绩信息表.学号=(SELECT 学号  FROM DELETED)

         DELETE FROM 学生信息表

         WHERE 学生信息表.学号=(SELECT 学号  FROM DELETED)

   END

GO

执行效果图如下：

2.执行以下DELETE语句，触发级联删除,并查看表。

--执行前查看表

SELECT * FROM 成绩信息表

SELECT * FROM 学生信息表

执行效果图如下：

--执行以下DELETE语句

DELETE FROM 学生信息表

WHERE 学号='14102'

成功删除执行效果图如下：

--执行后查看表

SELECT * FROM 成绩信息表

SELECT * FROM 学生信息表

执行效果图如下：

3.执行以下UPDATE语句，触发级联删除,并查看表。

--执行前查看表

SELECT * FROM 成绩信息表

SELECT * FROM 学生信息表

执行效果图如下：

--执行以下UPDATE语句

UPDATE 学生信息表

SET 学号='14102'

WHERE 学号='14101'

--执行后查看表

SELECT * FROM 成绩信息表

SELECT * FROM 学生信息表

--3.删除触发器，然后执行以下DELETE和UPDATE语句,并查看表。

--(1)删除触发器

DROP TRIGGER UP_DEL

--(2)执行以下DELETE语句，并查看表。

DELETE FROM 学生信息表

WHERE 学号='14102'

SELECT * FROM 学生信息表

答：执行失败，因为违反了fk_sc_xh约束，不属于级联删除，所以要将删除规则改为级联删除即可。

改为级联删除后再执行代码即可完成操作：

--(3)执行以下DELETE语句，并查看表。

UPDATE 学生信息表

SET 学号='14103'

WHERE 学号='14101'

SELECT * FROM 学生信息表

SELECT * FROM 成绩信息表

执行效果图如下：

答：执行失败，因为违反了PK__学生信息表__1CC396D2FC4A39AF主键约束，不能插入重复键。