第7章 存储过程和触发器

或
EXECUTE procGetStudentByName '王冲瑞' --返回王冲瑞同学的信息
【例7.3】
CREATE PROCEDURE procGetAvgGradeByNum
@Sno char(7), @avgGrade SMALLINT OUTPUT
AS
SELECT @avgGrade =AVG(Grade)
【例7.9】
ENABLE Trigger ALL ON ALL SERVER;
【例7.10】
ALTER TABLE SC DISABLE TRIGGER triSCInsert
4．删除触发器
【例7.11】 删除 DML 触发器。删除 SC表中的triSCInsert触发器。
（5）sp_stored_procedures 'procGetStudent'
--删除存储过程
EXEC sp_depends procGetStudent --查看procGetStudent所依赖的对象
DROP PROCEDURE procGetStudent --删除存储过程procGetStudent
AS
SELECT *
FROM Students
WHERE Sname LIKE RTRIM(@name);
--执行的方法：
EXECUTE procGetStudentByName; --返回所有学生的信息
或
EXECUTE procGetStudentByName '吴%'; --返回所有姓吴的学生信息
FROM SC
WHERE Sno=@Sno;
--执行存储过程，得到该学生的平均成绩。
DECLARE @averageGrade SMALLINT
EXEC procGetAvgGradeByNum '0602001', @averageGrade OUTPUT
PRINT '0602001的平均成绩为'+ STR(@averageGrade)
--在SQL Server Management Studio中创建DML触发器
CREATE TRIGGER triSCInsert --定义触发器所附着的表的名称“SC”
ON SC --定义触发器的类型
FOR INSERT --定义触发器的类型
--使用系统存储过程来查看用户创建的存储过程
（1） EXEC sp_help procGetStudent
（2） EXEC sp_helptext procGetStudent
（3）EXEC sp_depends procGetStudent
（4）Select * from sysobjects where name='procGetStudent' and type='p'
FROM Students
WHERE Sno=@number
--调用存储过程
EXECUTE procGetStudent '0602001';
【例7.1】
--创建存储过程的T-SQL语句如下：
USE StudentCourse
Go
CREATE PROC procAddCourse
END
--修改数据验证触发器
UPDATE SC SET Grade=80
WHERE Sno='06001' AND Cno='c1'
【例7.4】
CREATE TRIGGER triStudentDelete --定义名称为triStudentDelete的触发器
FOR UPDATE --定义触发器的类型
AS
IF UPDATE (Grade) --对分数列的更改
BEGIN
RAISERROR('不能修改课程分数',16,10)
ROLLBACK TRANSACTION
@Cno CHAR(3),@Cname VARCHAR(20),@PreCno CHAR(3),@Credit TINYINT
AS
INSERT INTO
Courses(Cno,Cname, PreCno, Credit)
VALUES (@Cno,@Cname, @PreCno, @Credit)
IF OBJECT_ID ('triSCInsert', 'TR') IS NOT NULL
DROP TRIGGER triSCInsert
【例7.12】
IF EXISTS ( SELECT *
FROM sys.triggers
WHERE parent_class = 0 AND name = 'safetyDB')
【例7.5】
CREATE TRIGGER safetyDB
ON DATABASE
FOR DROP_TABLE,ALTER_TABLE
AS
PRINT '禁止删除或修改数据库库中的表'
ROLLBACK;
--可以使用系统存储过程查看DML触发器。
（1）sp_help 'triStudentDelete'
AS
BEGIN
DECLARE @Sno char(7)
SELECT @Sno=ห้องสมุดไป่ตู้nserted.Sno FROM inserted
IF NOT EXISTS (SELECT Sno FROM Students WHERE Sno=@Sno)
PRINT '学生表中没有该学号的同学!'
ROLLBACK TRANSACTION
END
--插入数据验证触发器
Insert Into SC Values('0703005','C1',84)
--删除SC表中学号Sno参照外键FK_SNO
ALTER TABLE SC
DROP TRIGGER safetyDB ON DATABASE
ON Students --定义触发器所附着的表的名称Students
FOR DELETE --定义触发器的类型
AS
BEGIN
DECLARE @Sno CHAR(7)
AS
Select * FROM Courses
GO
--调用存储过程
EXECUTE procGetAllCourses;
（2）
--用T-SQL命令窗口创建存储过程
CREATE PROCEDURE procGetStudent
@number char(7)
AS
SELECT *
AS --下面是触发条件和触发器执行时要进行的操作
BEGIN
DECLARE @Sno char(7)
SELECT @Sno=inserted.Sno FROM inserted
IF NOT EXISTS (SELECT Sno FROM Students WHERE Sno=@Sno)
DROP CONSTRAINT FK_SNO
--用T-SQL命令窗口创建DML触发器
CREATE TRIGGER triSCUpdate --定义名称为triSCUpdate的触发器
ON SC --定义触发器所附着的表的名称“SC”
--执行存储过程的T-SQL语句如下：
EXEC procAddCourse
@Cno='C5', @Cname='C#程序设计', @PreCno='C2', @Credit=4
或
EXEC procAddCourse 'C5', 'C#程序设计', 'C2', 4
【例7.2】
CREATE PROCEDURE procGetStudentByName @name char(8)='%'
【例7.7】
DISABLE TRIGGER triSCInsert ON SC
GO
ENABLE TRIGGER triSCInsert ON SC
【例7.8】
DISABLE TRIGGER safetyDB ON DATABASE;
GO
ENABLE TRIGGER safetyDB ON DATABASE;
PRINT '学生表中没有该学号的同学!'
ELSE
INSERT INTO SC SELECT * FROM inserted --如果满足条件则插入数据
END
--插入语句测试触发器的动作
INSERT INTO SC VALUES('0701008','C1',84)
（2）sp_helptext 'triStudentDelete'
（3）sp_depends 'triStudentDelete'
sp_depends 'SC'
【例7.6】
ALTER TRIGGER triSCInsert
ON SC
INSTEAD OF INSERT
SELECT @Sno=deleted.Sno FROM deleted
DELETE SC WHERE Sno=@Sno
END