SQL触发器实例

SQL触发器实例

16.2.3 INSERT触发器
实例1 创建INSERT触发器
为STUDENT表创建触发器S_insert，当向STUDENT表中插入数据时，要求学号必须以“97”开头，且课程号CNO必须在COURSE表中，否则取消插入操作。实例代码如下。

CREATE TRIGGER S_insert
ON STUDENT
FOR INSERT AS
DECLARE @S_no VARCHAR(4), @S_cno INT
SELECT @S_no= SNO, @S_cno=CNO
FROM INSERTED
IF (LEFT(@S_no,2)!='97')
BEGIN
ROLLBACK TRANSACTION
RAISERROR('输入的学号：%s不是97级的学生，请确认后重新录入！',16,1, @S_no)
END
IF(@S_cno NOT IN (SELECT CNO FROM COURSE))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('输入的课程号：%d在COURSE表中不存在，请确认后重新录入！',16,1, @S_cno)
END

当通过如下语句向STUDENT表中插入数据时：

INSERT INTO STUDENT VALUES('9602','王永','机械工程','男',2,76,'必修')

由于插入数据的学号为“9602”，并不是以“97”开头，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消完成的工作，并执行RAISERROR语句给出错误信息。
运行结果如下。

输入的学号：9602不是97级的学生，请确认后重新录入！

当通过如下语句向STUDENT表中插入数据时：

INSERT INTO STUDENT VALUES('9702','王永','机械工程','男',12,76,'必修')

由于课程号12在COURSE表中不存在，所以执行S_insert触发器时，将执行“ROLLBACK TRANSACTION”语句，取消工作，并执行RAISERROR语句给出错误信息。运行结果：

输入的课程号：12在COURSE表中不存在，请确认后重新录入！

可以在一个表上创建多个触发器，数据库把一个表中所有触发器都看作同一事务的一部分。因此只要其中一个触发器执行了ROLLBACK TRAN SACTION语句，那么所有的操作（与该INSERT语句有关）都将被取消。
16.2.4 DELETE触发器
实例2 创建DELETE触发器
为STUDENT表创建DELETE触发器S_delete，当一次删除的记录大于一行或者删除记录的课程为本系教师所开设时，取消删除操作。实例代码：

CREATE TRIGGER S_delete
ON STUDENT
FOR DELETE AS
DECLARE @rowcount int
SELECT @rowcount=@@ROWCOUNT
IF @rowcount>1
BEGIN
ROLLBACK TRANSACTION
RAISERROR('当前要删除的记录数为%d,一次只允许删除一行记录！',16,1,
@rowcount)
END
DECLARE @S_dname VARCHAR(16), @S_cno INT
SELECT @S_dname=DNAME, @S_cno=CNO
FROM DELETED
IF(@S_cno IN (SELECT CNO FROM TEACHER WHERE DNAME=@S_dname))
BEGIN
ROLLBACK TRANSACTION
RAISERROR('删除记录的课程为本系即%s系教师所开设，不允许删除！',16,1,
@S_dname)
END

当通过如下语句从STUDENT表中删除数据时：

DELETE STUDENT WHERE SNO=’9706’

运行结果如下。

当前要删除的记录数为4,一次只允许删除一行记录！

当用户从STUDENT表执行DELETE语句时，DBMS就激活触发器S_delete，该触发器首先判断要删除记录的行数，只要多于一条，就取消删除操作。当删除的记录只有一行时，触发器继续判断删除的记录的课程是否为本系教师所开设，如果是，就取消删除操作，否则就完成删除操作。如下面的删除代码：

DELETE STUDENT WHERE SNO=’9705’ AND CNO=8

运行结果如下。

删除记录的课程为本系即电子工程 系教师所开设，不允许删除！

说明 在SQL Server中执行TRUNCATE TABLE语句，从表中删除所有的行时，不会触发DELETE触发器。

16.2.5 UPDATE触发器
特定的表上执行UPDATE语句时，会触发UPDATE触发器。UPDATE操作包括两个部分：先将需要更新的内容从表中删除，然后插入新值。因此UPDATE触发器同时涉及到删除表和插入表。
实例3 创建UPDATE触发器
为COURSE表创建UPDATE触发器C_update，当COURSE表的CNO和CNAME列（第1、2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事务。当其余的列（第3、4、5列）被更新时，触发器将更新前后的数据写入C_UpInfo表中。实例代码如下。
1．创建跟踪信息表C_UpInfo
CREATE TABLE C_UpInfo
(
Oper_Time smalldatetime,
Date_Type CHAR(3),
CNO int ,
CNAME char(30),
CTIME int,
SCOUNT int,
CTEST smalldatetime,
)
2．创建UPDATE触发器C_update
CREATE TRIGGER C_update
ON COURSE
FOR UPDATE AS
IF（COLUMNS_UPDATED()&3）>0

BEGIN
ROLLBACK TRANSACTION
RAISERROR('COURSE表的CNO列和CNAME列中的数据不允许被更新！',16,1)
END
IF（COLUMNS_UPDATED()&28）>0

BEGIN

INSERT INTO C_UpInfo(Oper_Time, Date_Type,CNO,CNAME,CTIME,SCOUNT,
CTEST)
SELECT GETDATE(),’OLD’,del.CNO,del.CNAME, del.CTIME, del.SCOUNT,
del.CTEST
FROM DELETED AS del

INSERT INTO C_UpInfo(Oper_Time, Date_Type,CNO,CNAME,CTIME,SCOUNT,
CTEST)
SELECT GETDATE(),’NEW’,ins.CNO,ins.CNAME, ins.CTIME, ins.SCOUNT,
ins.CTEST
FROM INSERTED AS ins
END
当通过下面的代码更新COURSE表的CNAME列的值：

UPDATE COURSE
SET CNAME = '线性代数基础'
WHERE CNO = 4

运行结果如下。
COURSE表的CNO列和CNAME列中的数据不允许被更新！
此时查看CNO＝4的课程名称：

SELECT CNAME FROM COURSE WHERE CNO=4

运行结果如图16.2所示。

图16.2 CNO＝4的课程名称
可见，CNAME列的值并没有被更新。而通过下面代码更新CNO和CNAME列以外的信息时：

UPDATE COURSE
SET CTIME=32,SCOUNT=90, CTEST='2006-9-15'
WHERE CNO = 4

此时查看CNO = 4的课程信息：

SELECT * FROM COURSE WHERE CNO=4

运行结果如图16.3所示：

图16.3 CNO = 4的课程信息
可见，该记录已经被更新。
查看跟踪信息表C_UpInfo中的数据：

SELECT * FROM C_UpInfo

运行结果如图16.4所示。

图16.4 表C_UpInfo中的数据
该表详细记录了COURSE表的更新时间以及更新前后的数据