SQL Server 数据库实验课第八周——（第五章续）触发器 + 存储过程

上接SQL Server 数据库实验课第七周——第五章数据库完整性

5.7触发器

触发器（Trigger）
任何用户对表的增、删、改操作均由服务器自动激活相应的触发器
触发器可以实施更为复杂的检查和操作，具有更精细和更强大的数据控制能力

5.7.1 定义触发器

触发器又叫事件——条件——动作规则。当特定的系统事件发生时，对规则的条件进行检查。如果条件成立则执行规则中的动作，否则不执行该动作规则中的动作体可以很复杂，通常是一段SQL存储过程
语法格式

CREATE TRIGGER <触发器名>  
{BEFORE | AFTER} <触发事件> ON <表名>
REFERENCING NEW|OLD ROW AS<变量>
FOR EACH  {ROW | STATEMENT}
[WHEN <触发条件>]<触发动作体>

触发事件
触发事件可以是INSERT、DELETE或UPDATE，也可以是这几个事件的组合。还可以UPDATE OF<触发列，…>，即进一步指明修改哪些列时激活触发器
AFTER/BEFORE是触发的时机
AFTER表示在触发事件的操作执行之后激活触发器
BEFORE表示在触发事件的操作执行之前激活触发器
触发器类型
行级触发器（FOR EACH ROW）
语句级触发器（FOR EACH STATEMENT）
例如,在例5.11的TEACHER表上创建一个AFTER UPDATE触发器，触发事件是UPDATE语句：

UPDATE TEACHER SET Deptno=5;

假设表TEACHER有1000行
如果是语句级触发器，那么执行完该语句后，触发动作只发生1次
如果是行级触发器，触发动作将执行1000次
例5.21当对表SC的Grade属性进行修改时，若分数增加了10%则将此次操作记录到另一个表SC_U（Sno,Cno,Oldgrade,Newgrade）中，其中Oldgrade是修改前的分数，Newgrade是修改后的分数。
标准SQL

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC--触发事件
REFERENCE
	OLDROW AS OldTuple,
	NEWROW AS NewTuple
FOR EACH ROW--行级触发器
WHEN(NEWTuple.Grade>=1.1*OldTuple.Grade)
	INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
	VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

标准SQL执行后报错
T-SQL

--建表
CREATE TABLE SC_U(
	Sno CHAR(9),
	Cno CHAR(4),
	OldGrade SMALLINT,
	NewGrade SMALLINT,
	FOREIGN KEY(Sno) REFERENCES Student(Sno),
	FOREIGN KEY(Cno) REFERENCES Course(Cno)
);
--建立触发器
CREATE TRIGGER SC_T
ON SC AFTER 
UPDATE AS
--declare @变量名称 变量类型
DECLARE @Sno CHAR(15)
DECLARE @Cno CHAR(4)
DECLARE @OldGrade SMALLINT
DECLARE @NewGrade SMALLINT

SELECT @OldGrade=Grade FROM DELETED
SELECT @NewGrade=Grade FROM INSERTED;
SELECT @Sno=Sno FROM SC
SELECT @Cno=Cno FROM SC
IF(@NewGrade>=1.1*@OldGrade)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
VALUES(@Sno,@Cno,@OldGrade,@NewGrade)

SC表


例5.22 将每次对表Student的插入操作所增加的学生个数记录到表StudentInsertLog中。
标准SQL

CREATE TRIGGER Student_Count
AFTER INSERT ON Student
NEW TABLE AS DELTA
FOR EACH STATEMENT
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM DELTA

T-SQL

CREATE TABLE StudentInsertLog(--建表储存个数
Numbers INT
);
CREATE TRIGGER Student_Count ON Student
AFTER INSERT 
AS
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM Student

INSERT INTO Student
VALUES('1111','11','男',18,'CS');--第一次插入后一共有7个学生

INSERT INTO Student
VALUES('222','222','男',18,'CS');
SELECT * FROM StudentInsertLog--第二次插入后一共有8个学生

例5.23 定义一个BEFORE行级触发器，为教师表Teacher定义完整性规则“教授的工资不得低于4000元，如果低于4000元，自动改为4000元”。
标准SQL

CREATE TRIGGER Insert_or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
REFERENCING NEW row AS newTuple
FOR EACH ROW
BEGIN
	IF(newtuple='教授')AND(newtuple.Sal<4000)
	THEN newtuple.Salary=4000;
	END IF
END;

T-SQL
先建表

CREATE TABLE Teacher
(
	Name CHAR(20),
	Salary INT,
	Job CHAR(20)
)

创建触发器

CREATE TRIGGER Insert_or_Update_Sal ON Teacher
FOR INSERT , UPDATE 
AS DECLARE @Salary SMALLINT
   DECLARE @Job CHAR(10)
   IF (UPDATE(Salary))
   SELECT @Salary=Salary FROM INSERTED
   SELECT @Job=Job FROM Teacher
	IF(@Job='教授')AND(@Salary<4000)
	UPDATE Teacher
	SET Salary=4000
	WHERE Job='教授'AND Salary<4000

插入工资等于3000的教授

5.7.2 激活触发器

触发器的执行，是由触发事件激活的，并由数据库服务器自动执行！
一个数据表上可能定义了多个触发器，遵循如下的执行顺序:
（1） 执行该表上的BEFORE触发器;
（2） 激活触发器的SQL语句;
（3） 执行该表上的AFTER触发器。

5.7.3 删除触发器

删除触发器的SQL语法：

DROP TRIGGER <触发器名> ON <表名>;

8.3 存储过程和函数

8.3.1 存储过程

存储过程：由过程化SQL语句，经编译和优化后存储在数据库服务器中，可以被反复调用，运行速度较快。
优点：
（1）运行效率高
（2）降低了客户机和服务器之间的通信量
（3）方便实施企业规则

（1）创建存储过程

CREATE OR REPLACE PROCEDURE 过程名([参数1,参数2,...]) AS <过程化SQL块>；

例8.8 利用存储过程来实现下面的应用：从账户1转指定数额的款项到账户2中。
1.建立新表，创建两个用户

DROP TABLE IF EXISTS Account;
CREATE TABLE Account
(
	accountnum CHAR(3),--编号
	total FLOAT--余额
);
INSERT INTO Account VALUES(101,50);
INSERT INTO Account VALUES(102,100);
SELECT * FROM Account

2.建立存储过程

IF(EXISTS(SELECT * FROM sys.objects WHERE name='Proc_TRANSFER'))
	DROP PROCEDURE Proc_TRANSFER
	GO 
	CREATE PROCEDURE Proc_TRANSFER
	@inAccount INT,@outAccount INT,@amount FLOAT--转入账户，转出账户，转账额度
	AS
	BEGIN TRANSACTION TRANS
		DECLARE --定义变量
		@totalDepositOut FLOAT,
		@totalDepositIn FLOAT,
		@inAccountnum INT;
		--检查转出账户余额
		SELECT @totalDepositOut=total FROM Account WHERE accountnum=@outAccount;
		--如果转出账户不存在或没有存款
		IF @totalDepositOut IS NULL
			BEGIN
			PRINT'转出账户不存在或账户没有存款'
			ROLLBACK TRANSACTION TRANS;
			RETURN;
		END;
		--如果账户存款不足
		IF @totalDepositOut<@amount
			BEGIN
			PRINT'账户存款不足'
			ROLLBACK TRANSACTION TRANS;
			RETURN;
		END;
		--如果条件无异常，开始转账
		BEGIN	
			UPDATE Account SET total =total-@amount WHERE accountnum=@outAccount;--修改转出账户余额，减去转出额
			UPDATE Account SET total =total+@amount WHERE accountnum=@inAccount;--修改转入账户余额，增加转入额
			PRINT'转账完成，请取走银行卡'
			COMMIT TRANSACTION TRANS;--提交转账事物
			RETURN;
		END;

3.测试102向101转账50
T-SQL 使用EXEC执行存储过程
sql server 中的EXEC有什么用？！_百度知道

EXEC	Proc_TRANSFER
	@inAccount = 101,	--转入账户
	@outAccount = 102,	--转出账户
	@amount = 50		--转出金额
SELECT * FROM Account

再转51 余额不足

（2）执行存储过程

 CALL/PERFORM  PROCEDURE 过程名([参数1,参数2,...])；

使用CALL或者PERFORM等方式激活存储过程的执行
数据库服务器支持在过程体中调用其他存储过程
例8.9 从账户01003815868转10000元到01003813828账户中。
标准SQL

CALL PROCEDURE       TRANSFER(01003813828,01003815868,10000);

T-SQL

INSERT INTO Account VALUES(01003815868,20000)
INSERT INTO Account VALUES(01003813828,10000)
EXEC	Proc_TRANSFER
		@inAccount = 01003813828,	--转入账户
		@outAccount = 01003815868,	--转出账户
		@amount = 10000		--转出金额

SELECT * FROM Account

（3）修改存储过程

ALTER PROCEDURE 过程名1  RENAME TO 过程名2;

（4）删除存储过程

DROP  PROCEDURE 过程名()；

8.3.2 函数

函数和存储过程的异同
同：都是持久性存储模块
异：函数必须指定返回的类型
1.函数的定义语句格式

CREATE OR REPLACE FUNCTION 函数名 ([参数1,参数2,…]) RETURNS <类型>  AS <过程化SQL块>;

2.函数的执行语句格式

CALL/SELECT 函数名 ([参数1,参数2,…]);

3.修改函数
（1）重命名

ALTER FUNCTION 过程名1 RENAME TO 过程名2;

（2）重新编译

ALTER FUNCTION 过程名 COMPILE;