企业官网建设流程全解析

简介：西北工业大学《数据库原理》实验报告（第五部分）提供了一份完整的数据库操作实践案例，适合正在学习SQL Server存储过程与触发器的本科学生作为参考。文档围绕视图更名、带参数存储过程创建与加密、系统存储过程查看文本信息展开，并针对SC表、S表设计了INSERT、DELETE、UPDATE三类触发器，包含禁启、删除及自动维护成绩统计表CAvgGrade的综合应用。资源包中文件共1个doc文档，大小231KB，内容包含每一步的创建语句、执行命令和验证结果，重点展示触发器中instead of与after的使用差异、rollback与raiserror的错误处理方式。目前已有712人学习下载，清晰还原数据库实验从建表到触发器联动的完整流程，能帮助读者对照练习并排查同类实验中的常见问题。

1. 数据库实验里最值得抠的细节，藏在存储过程和触发器里

很多同学做数据库课程设计时能把增删改查写得很顺，一到《数据库原理》实验里碰存储过程和触发器，就卡在“明明照抄了，为什么行为不对”。西北工业大学这份数据库实验报告把视图改名、带参数存储过程、加密存储过程、增删改触发器、自动汇总表和游标调薪连在一起，表面是 T-SQL 语法训练，实际考的是四件事：inserted 和 deleted 临时表怎么用、INSTEAD OF 与 AFTER 触发器的触发时机、NULL 与 0 在统计里的不同语义、以及批量生成数据时怎么控制编号。适合正在复习数据库面试题的人，也适合在老系统里维护触发器、经常被“改一条数据引发连锁反应”困扰的工程师。下面不按报告顺序平铺，而是按“先跑通、再看边界”的方式重排。

2. 存储过程：从参数传值、加密到删除，把 SQL Server 例程用明白

2.1 jsearch：带参数的三表关联，先理清 join 方向

CREATE PROCEDURE jsearch @search_jno NCHAR(20) AS BEGIN SELECT j.jname, s.sname, p.pname FROM dbo.s AS s JOIN dbo.p AS p JOIN dbo.j AS j JOIN dbo.spj AS spj ON spj.jno = j.jno AND spj.sno = s.sno AND spj.pno = p.pno WHERE spj.jno = @search_jno; END; GO EXEC jsearch @search_jno = 'J1';

代码里的@search_jno是输入参数，NCHAR(20)与工程号列类型保持一致，中文环境下优先用 N 前缀类型，避免隐式转换把索引拖慢。dbo.s、dbo.p、dbo.j、dbo.spj是实验库里的简写表名，生产环境不建议这样命名。JOIN 条件把 spj 当作事实表，再连接三个维度表，最后用 WHERE 过滤参数。原报告里EXECjsearch中间没有空格，T-SQL 会把它解析成一个标识符并报“不是可识别的内置函数名”，应当写成EXEC jsearch。调用时可以用位置参数EXEC jsearch 'J1'，也可以用命名参数EXEC jsearch @search_jno = 'J1'，后者在过程增加参数时更不容易传错。

提示：视图改名EXEC sp_rename 'dbo.V_SPJ', 'V_SPJ_三建'和存储过程共用 sys.objects 命名空间，如果表名或视图名里带中文，SQL Server 会自动接受中文标识符，但在跨脚本引用时最好用方括号包起来，比如[V_SPJ_三建]。

2.2 加密存储过程 jmsearch 与 sp_helptext 能看到的和看不到的

CREATE PROCEDURE jmsearch WITH ENCRYPTION AS BEGIN SELECT * FROM dbo.s WHERE city = ''; END; GO EXEC sp_helptext 'jsearch'; EXEC sp_helptext 'jmsearch'; GO DROP PROCEDURE jmsearch;

用WITH ENCRYPTION创建之后，sp_helptext 'jmsearch'不再返回正文，而是提示对象已加密；sp_helptext 'jsearch'仍然可以看到完整定义。这正是实验要验证的差异。

这里要提醒一句：WITH ENCRYPTION并不是真正不可逆的安全机制，它只是把 sys.sql_modules 里的定义文本做混淆，熟悉 SQL Server 底层的人可以用 DAC 连接或第三方工具反解。不要把数据库连接串、口令这类敏感信息写死在存储过程里，它只能防手贱的人，防不了有心人。

2.3 存储过程排错：先查对象、再查权限、最后查调用方式

存储过程运行时报错，常见几类：

• 找不到对象：CREATE PROCEDURE与调用方不在同一个数据库，或者表名里带了多余前缀。可以先查OBJECT_ID('jsearch')是否存在。
• 参数错误：EXEC jsearch @search_jno = 'J1'没问题，但如果写成EXEC jsearch 'J1', 'J2'就会报告参数个数不匹配，因为 jsearch 只声明了一个参数。
• 加密过程无法修改：想改 jmsearch 只能DROP后重建，或者提前保存脚本。这也是很多团队禁止在正式库使用WITH ENCRYPTION的原因。
• 视图改名带来的依赖失效：sp_rename只会改对象名，不会同步修改已经编译好的存储过程。如果后续有人把V_SPJ改成V_SPJ_三建，引用旧视图名的存储过程不会立刻报错，但执行时会提示“找不到对象”。排查时用sys.procedures和sys.sql_modules看元数据，再逐步剥离 WHERE 条件定位问题。

3. 触发器边界：INSTEAD OF 与 AFTER 到底怎么选

3.1 insert_s：用 INSTEAD OF 拦截非法课程编号

CREATE TRIGGER insert_s ON SC INSTEAD OF INSERT AS BEGIN IF EXISTS ( SELECT 1 FROM inserted AS i WHERE i.cno NOT IN (SELECT cno FROM C) ) BEGIN PRINT '不能插入这样的记录！！'; ROLLBACK TRANSACTION; END ELSE BEGIN PRINT '记录插入成功！！'; END END; GO

执行验证：

INSERT INTO SC(sno, cno, grade) VALUES (95009, 8, 98); INSERT INTO SC(sno, cno, grade) VALUES (95009, 5, 98);

INSTEAD OF INSERT会让原始 INSERT 不生效，触发器体成为新的执行体。inserted表里保存的是用户试图写入的整行，这里用cno NOT IN判断课程编号是否在 C 表存在。注意原报告里列名被 OCR 成o，结合上下文应为cno；如果课程表中允许 NULL，NOT IN遇到 NULL 会产生“不返回任何行”的坑，更稳妥的写法是NOT EXISTS。，ROLLBACK TRANSACTION会把触发器所在的隐式事务回滚。由于原始插入还没有发生，非法记录最终不会落地。还有一个容易被忽略的问题：ELSE 分支只打印“记录插入成功”，合法记录实际上也不会写入，因为 INSTEAD OF 触发器没有继续执行 INSERT。如果业务上要求合法记录正常落库，需要在 ELSE 里补一条INSERT INTO SC(sno, cno, grade) SELECT sno, cno, grade FROM inserted;。

3.2 两个 DELETE 触发器：禁止删除与级联清理

CREATE TRIGGER dele_s1 ON S INSTEAD OF DELETE AS BEGIN PRINT '禁止删除'; ROLLBACK TRANSACTION; END; GO CREATE TRIGGER dele_s2 ON S AFTER DELETE AS BEGIN DELETE FROM SC WHERE sno IN (SELECT sno FROM deleted); END; GO

验证语句：

DELETE FROM S WHERE sno = '95001';

为什么同样对 DELETE 做文章，一个用 INSTEAD OF，一个用 AFTER？因为需求不同。dele_s1 要禁止任何删除动作，直接替代原操作；dele_s2 要先让 S 表的删除成功，再找到被删除学生的选课记录并清理。注意在 SQL Server 中，外键约束的检查先于 AFTER 触发器；如果 SC 表上存在指向 S 表的外键且没有级联删除，dele_s2 根本触发不到，DELETE 会因为外键冲突直接报错。这也是原实验里要先“删除 SC 表上的外键约束”的原因。

补充一个实际经验：同表上多个 AFTER 触发器的执行顺序在 SQL Server 中不被严格保证，不要依赖触发器的先后关系。需要严格顺序时，应该把逻辑合并到一个触发器里，或者用存储过程统一编排。

3.3 update_s：禁止更新字段，以及禁用/删除触发器

CREATE TRIGGER update_s ON S INSTEAD OF UPDATE AS BEGIN IF UPDATE(sdept) BEGIN RAISERROR('sdept 不能被修改', 10, 1); END ELSE BEGIN UPDATE S SET sno = i.sno, sname = i.sname, sdept = i.sdept, sage = i.sage FROM inserted AS i WHERE S.sno = i.sno; END END; GO

这里和原报告的差别是：原报告只写了 RAISERROR，没有在 ELSE 分支里补正常的 UPDATE。INSTEAD OF 触发器一旦建立，所有对 S 表的 UPDATE 都会走触发器，如果报错之外的分支为空，其他字段的更新会被“吞掉”，表现就是更新语句执行成功但数据没变。我一般会把源表主键对应的新值从 inserted 取出来逐列写回，逻辑上和 AFTER 等价，但多一层控制。

随后禁用和删除：

DISABLE TRIGGER update_s ON S; GO UPDATE S SET sdept = 'CS1' WHERE sno = '95002'; GO DROP TRIGGER update_s; GO

DISABLE TRIGGER只禁用不删除，元数据仍在，重新启用用ENABLE TRIGGER update_s ON S;即可。原报告里删除语句没有 WHERE，会把整张表都刷成CS1，实验环境数据少看不出问题，真实环境一条漏掉 WHERE 的 UPDATE 会把全表业务字段冲掉。测试时习惯性带上主键过滤，能避免很多事故。

4. 触发器驱动的汇总表：把 SC 表的增删改折算到 CAvgGrade

4.1 建表与触发器框架

CREATE TABLE CAvgGrade ( cno CHAR(10) PRIMARY KEY, Snum INT, examSNum INT, avgGrade INT ); GO CREATE TRIGGER update_sc_cavggrade ON SC FOR INSERT, DELETE, UPDATE AS BEGIN SET NOCOUNT ON; DECLARE @cno CHAR(10); SELECT TOP 1 @cno = cno FROM inserted; IF @cno IS NULL SELECT TOP 1 @cno = cno FROM deleted; IF @cno IS NOT NULL BEGIN UPDATE CAvgGrade SET Snum = ( SELECT COUNT(*) FROM SC WHERE cno = @cno ), examSNum = ( SELECT COUNT(*) FROM SC WHERE cno = @cno AND grade >= 0 ), avgGrade = ( SELECT AVG(grade) FROM SC WHERE cno = @cno AND grade >= 0 ) WHERE cno = @cno; END; END; GO

这里为了可读性先只取第一条变化的课程编号，真正的多行安全问题放到 4.3 节处理。FOR INSERT, DELETE, UPDATE是 SQL Server 写法，作用等同于AFTER INSERT, DELETE, UPDATE。SET NOCOUNT ON避免每个受影响的 DML 都返回“N 行受影响”的消息，否则在 SSMS 输出里会非常嘈杂。

grade >= 0是整个触发器最容易被写错的地方。0 表示确实参加考试且得分为 0，要计入平均分；NULL 表示缺考，不能计入。如果写WHERE grade IS NOT NULL，会把 0 分也纳入，方向没错；如果写WHERE grade > 0，就会丢掉 0 分学生，平均分被抬高。AVG(grade)本身会忽略 NULL，但配合grade >= 0能让计数统计和平均值使用同一批数据，语义更统一。

4.2 验证：插入、删除、更新三种操作都要看

INSERT INTO CAvgGrade VALUES ('1', 0, 0, NULL); GO INSERT INTO SC(sno, cno, grade) VALUES ('95004', '1', 65); GO SELECT * FROM CAvgGrade WHERE cno = '1'; GO DELETE FROM SC WHERE sno = '95001'; GO UPDATE SC SET grade = 99 WHERE sno = '95004'; GO

从报告里的截图看，插入 65 分后 Snum 和 examSNum 都变成 1，avgGrade 变为 65；删除一条之后人数减一；更新成绩后平均分重新计算。

这里还隐藏着一个类型问题：avgGrade 列定义成 INT，AVG(grade)计算出的 66.7 会被舍入成 67。如果业务需要保留小数，列类型应改为DECIMAL(6, 2)，否则每一次自动汇总都在悄悄损失精度。

4.3 多行 DML 下的触发器保护

批量写数据时，inserted 和 deleted 都是多行结果集，不能假设只有一行。常见做法是用临时表收集所有受影响的课程编号，再用游标逐门课刷新：

ALTER TRIGGER update_sc_cavggrade ON SC FOR INSERT, DELETE, UPDATE AS BEGIN SET NOCOUNT ON; IF OBJECT_ID('tempdb..#changed') IS NOT NULL DROP TABLE #changed; CREATE TABLE #changed (cno CHAR(10)); INSERT INTO #changed(cno) SELECT cno FROM inserted UNION SELECT cno FROM deleted; DECLARE @cno CHAR(10); DECLARE cur CURSOR LOCAL FOR SELECT cno FROM #changed; OPEN cur; FETCH NEXT FROM cur INTO @cno; WHILE @@FETCH_STATUS = 0 BEGIN UPDATE CAvgGrade SET Snum = (SELECT COUNT(*) FROM SC WHERE cno = @cno), examSNum = (SELECT COUNT(*) FROM SC WHERE cno = @cno AND grade >= 0), avgGrade = (SELECT AVG(grade) FROM SC WHERE cno = @cno AND grade >= 0) WHERE cno = @cno; FETCH NEXT FROM cur INTO @cno; END; CLOSE cur; DEALLOCATE cur; END; GO

游标方案能解决多行问题，但触发器里频繁开游标会增加锁等待和数据库死锁的概率。另一个思路是放弃触发器，改成每晚定时任务全量重算，或直接在查询层用聚合视图。触发器适合“变更必须立即生效”的场景，不适合大数据量高频写。

5. 用生成 1000 条员工数据的场景，把 UDF、存储过程和游标串起来

5.1 generateEID：实验要函数，报告却用了存储过程

CREATE PROCEDURE generateEID AS BEGIN DECLARE @i INT = 0; WHILE @i < 1000 BEGIN INSERT INTO dbo.employee(eID, eName, salary) VALUES ( 20160001 + @i, 'name' + CAST(@i AS NVARCHAR(20)), 2000 + CAST(FLOOR(RAND() * 3001) AS INT) ); SET @i = @i + 1; END; END; GO EXEC generateEID;

这个场景很典型：题干要求自定义函数 generateEID，但实验报告里最终落在存储过程上。原因是 SQL Server 的自定义函数不能在函数体内执行 INSERT，无法直接“自动生成并插入员工数据”，所以把生成 ID 的逻辑放进存储过程循环插入是更实际的折中。若要严格按题目要求，可以考虑一个内联表值函数返回 1000 行序号，再由存储过程读取并插入。年份部分也不要写死：用DATEPART(YEAR, GETDATE())拼出前四位，后四位从 0001 开始递增，这样跨年后编号不会撞。原报告举的例子“2015 年插入的第一条数据是 20050001”里 2005 与 2015 不一致，应该是录入笔误，按“前四位年份 + 四位序号”的规则写即可。

5.2 游标调薪：用 CURRENT OF 逐行更新工资

DECLARE mycursor CURSOR LOCAL FOR SELECT salary FROM dbo.employee; OPEN mycursor; DECLARE @salary INT; FETCH NEXT FROM mycursor INTO @salary; WHILE @@FETCH_STATUS = 0 BEGIN IF @salary < 3000 UPDATE dbo.employee SET salary = salary + 300 WHERE CURRENT OF mycursor; ELSE IF @salary < 4000 UPDATE dbo.employee SET salary = salary + 200 WHERE CURRENT OF mycursor; ELSE UPDATE dbo.employee SET salary = salary + 50 WHERE CURRENT OF mycursor; FETCH NEXT FROM mycursor INTO @salary; END; CLOSE mycursor; DEALLOCATE mycursor;

游标声明里的LOCAL限制了它的作用域只在当前批处理内，结束后自动释放。CURRENT OF mycursor根据游标当前位置定位，更新后游标结果集中该行 salary 会变化，但后续 FETCH 仍然按原游标结果集移动。实际生产里，如果只是按工资区间整体调薪，一条 UPDATE 配合 CASE 表达式就够了，既减少游标逐行造成的锁竞争，也降低数据库死锁风险。以后遇到这类需求，我先问自己：能不能用一条 UPDATE 写完？能，就不写游标。

本文还有配套的精品资源，点击获取