简介这份西北工业大学《数据库原理》实验报告第五部分面向正在学习数据库课程的高校学生与自学者聚焦数据表创建管理、存储过程与触发器三大核心操作帮助读者在 SQL Server 环境下完成从建表到编写业务逻辑的完整训练。资源包共1个doc文件约231KB内容为可直接参考的实验报告文档涵盖 sp_rename 重命名视图、带参数存储过程 jsearch 与加密存储过程 jmsearch 的创建执行、sp_helptext 查看文本以及 insert_s、dele_s1、dele_s2、update_s 等触发器的编写与验证还涉及 CAvgGrade 成绩统计表的自动更新触发器设计。目前已有716人学习适合需要对照实验步骤、理解存储过程参数传递与触发器事务回滚机制、查漏补缺的读者可借此掌握数据库对象的设计思路与调试方法。1. 从一份实验报告说起存储过程与触发器到底怎么落地很多人第一次接触数据库编程都是从一份实验报告开始的。这份《数据库原理》实验报告围绕 SPJ 和 Student 两个经典数据库展开把存储过程、触发器、游标、用户自定义函数这几块内容串成了一条完整的动手链路。它解决的不是“数据库是什么”这种概念问题而是“给你一个具体业务场景怎么用 T-SQL 把逻辑写进去并跑通”。适合正在学数据库课程、准备实验验收或者工作中需要快速补齐存储过程和触发器实操经验的人。我拆完这份报告最大的感受是语法本身不难难的是搞清楚 inserted 和 deleted 这两张临时表在什么时候有值、什么时候为空以及 instead of 和 after 触发器的行为差异。下面按实验内容的推进顺序把每一步的原理、代码和参数设置讲清楚。2. 存储过程从 jsearch 到加密与删除的完整操作链存储过程是这次实验的第一块硬骨头。报告里涉及了带参数查询、加密、查看定义、执行和删除基本覆盖了存储过程的生命周期。我按自己的理解重新梳理一遍把每一步为什么这么做讲透。2.1 带参数存储过程 jsearch 的创建与调用jsearch 的需求是输入一个工程代号返回该工程对应的供应商名称、零件名称和工程名称。这需要联合 S、P、J、SPJ 四张表。创建语句如下create procedure jsearch(search_jno nchar(20)) as begin select j.jname, s.sname, p.pname from s, p, j, spj where spj.jno search_jno and spj.jno j.jno and spj.sno s.sno and spj.pno p.pno end逻辑说明search_jno是输入参数类型nchar(20)对应 J 表中 jno 字段的定义。四表等值连接的条件必须写全少一个就会产生笛卡尔积。调用时用EXEC jsearch search_jnoJ1注意参数名要和定义时一致否则 SQL Server 会按位置匹配容易传错。参数设置上有一个容易忽略的点如果 J 表的 jno 是char类型而参数用了nchar在某些排序规则下会出现隐式转换导致索引失效。实验环境数据量小感觉不到但生产环境里这种细节就是性能杀手。2.2 加密存储过程 jmsearch 与 sp_helptext 的验证jmsearch 的要求是返回供应商所有信息并且加密。代码create procedure jmsearch with encryption as begin select * from S where city endwith encryption这个选项一旦加上存储过程定义文本就被混淆存储用sp_helptext查不到内容。报告里用exec sp_helptext jsearch和exec sp_helptext jmsearch做对比验证——前者能正常显示源码后者会报“对象注释已加密”之类的错误。这个对比设计得挺好一眼就能看出加密生效了。但要注意with encryption不是安全屏障。有经验的人可以通过系统视图或者第三方工具还原部分逻辑它更多是防止随意查看不是加密保护。另外加密之后如果想修改存储过程必须先drop再重建不能直接alter因为原始文本已经没了。这是血泪经验我见过有人加密完想改一个条件折腾半天才发现改不了。2.3 执行、删除与 sp_rename 的配合使用执行 jmsearch 用exec jmsearch即可。删除用drop procedure jmsearch。报告开头还提到了用sp_rename把视图 V_SPJ 改名为 V_SPJ_三建exec sp_rename v_spj, v_spj_三建sp_rename的第一个参数是原对象名第二个是新名。注意改名后所有引用旧名的存储过程、视图都会失效需要手动更新。实验里只改了视图名没有涉及依赖对象所以没暴露这个问题。实际项目中改表名或列名之前一定要先查sys.sql_expression_dependencies看看谁引用了它。存储过程这块的验收标准很明确jsearch 能查出 J1 对应的三列信息jmsearch 执行有结果但 sp_helptext 查不到定义删除后 exec 报对象不存在。三步都过了这部分就没问题。3. 触发器inserted 与 deleted 临时表的实战用法触发器是这份报告的重头戏占了 40 分。报告里覆盖了 INSERT、DELETE、UPDATE 三种事件以及 instead of 和 after 两种触发时机。我把它们按行为分类来讲这样更容易理解什么时候该用哪种。3.1 instead of 触发器拦截非法插入与禁止删除insert_s 触发器的需求是向 SC 表插入记录时如果课程号不在 C 表中就提示错误并回滚。代码create trigger insert_s on SC instead of insert as begin if (exists(select * from inserted where o not in (select o from C))) begin print 不能插入这样的记录 rollback transaction end else print 记录插入成功 end关键点在于instead of它替代了原始的 INSERT 操作。也就是说数据不会自动写入 SC 表需要你在触发器里手动写 INSERT 语句。报告里的代码只做了检查没有真正插入数据所以验证时“成功”的那条记录其实也没进表。这是实验代码的一个简化实际使用时要在 else 分支里补上insert into SC select * from inserted。dele_s1 触发器禁止删除 S 表记录同样用instead of delete加rollback transaction。验证时执行delete from s where sno95001会看到“禁止删除”的提示S 表数据不变。3.2 after 触发器级联删除与字段更新拦截dele_s2 是 after delete 触发器作用是删除 S 表记录时自动删除 SC 表中该学生的选课记录create trigger dele_s2 on S after delete as delete from SC where sno in (select sno from deleted)这里deleted临时表存放的是被删除的 S 表记录。after 触发器的特点是主表的删除操作已经完成触发器在之后执行。所以如果 SC 表有外键约束指向 S 表删除 S 表记录会先被外键拦住根本走不到触发器。报告里第一步就是“删除 SC 表上的外键约束”原因就在这里。update_s 触发器禁止修改 sdept 字段用的是instead of update加if update(sdept)判断create trigger update_s on S instead of update as if update(sdept) begin raiserror(sdept 不能被修改, 10, 1) endupdate()函数只在触发器内部有效用来判断某个列是否在 UPDATE 语句中被提及。注意它不关心值有没有实际变化只要 SET 子句里出现了这个列名就返回真。raiserror的严重级别设为 10是信息性消息不会中断执行。如果要强制阻止应该用级别 16 并配合 rollback。3.3 触发器的禁用与删除禁用触发器用disable trigger update_s on S禁用后更新 sdept 会正常执行。删除用drop trigger update_s。这里有个顺序问题必须先禁用再验证验证完再删除。如果直接删除就没法做“禁用后是否还工作”这个对比了。3.4 统计表 CAvgGrade 的自动维护触发器这是触发器部分最复杂的一个。需求是SC 表有插入、删除或成绩更新时自动更新 CAvgGrade 表中的选课人数、考试人数和平均成绩。报告里的代码有几个明显问题我按正确逻辑重写一下create trigger update_sc_cavggrade on SC after insert, delete, update as begin declare cno char(10) declare ssum int declare examssum int declare avggrade int select cno o from inserted if cno is null select cno o from deleted select ssum count(*) from SC where o cno select examssum count(*) from SC where o cno and cgrade 0 select avggrade avg(cgrade) from SC where o cno and cgrade 0 update CAvgGrade set Ssum ssum, examSsum examssum, avgGrade avggrade where o cno end逻辑说明先从 inserted 取课号如果 inserted 为空删除操作就从 deleted 取。然后分别统计总选课人数、参加考试人数grade 0因为 NULL 表示未考试0 分是有效成绩再算平均分。最后更新 CAvgGrade 表。参数设置上avggrade声明为 int 类型但 AVG 返回的是数值类型如果成绩有小数会被截断。实际使用应该用 decimal。另外这个触发器一次只能处理一个课号的变化批量插入或删除多个课号时会漏更新。生产环境需要用游标或者基于集合的 UPDATE 语句来处理。4. 游标与自定义函数批量数据处理的两个关键工具实验最后一部分是 works 数据库的员工涨薪和 ID 自动生成。这两块分别用到了游标和存储过程我分开讲。4.1 用游标实现按工资区间涨薪需求是按工资区间调整3000 以下涨 3003000 到 4000 涨 2004000 及以上涨 50。报告用游标逐行处理use work go declare mycursor cursor for select salary from employee open mycursor declare salary int fetch next from mycursor into salary while FETCH_STATUS 0 begin if salary 3000 update employee set salary salary 300 where current of mycursor else if salary 4000 update employee set salary salary 200 where current of mycursor else update employee set salary salary 50 where current of mycursor fetch next from mycursor into salary end close mycursor deallocate mycursorwhere current of mycursor是游标定位更新的语法直接修改当前行。注意游标声明后必须 open、fetch、close、deallocate 四步走完少一步都会占用资源。1000 条数据用游标没问题但十万条以上就要考虑用基于集合的 UPDATE 加 CASE WHEN 来替代否则性能下降很明显。4.2 自动生成员工 ID 的存储过程需求是生成 8 位员工 ID前四位是年份后四位从 0001 递增。报告用存储过程加循环实现use work go create procedure generateEID as begin declare i int set i 0 while (i 1000) begin insert into dbo.employee values (20160001 i, name CAST(i as nchar(20)), 2000 CAST(FLOOR(rand() * 3001) as int)) set i i 1 end endrand()生成 0 到 1 之间的随机数乘以 3001 再取整得到 0 到 3000 的整数加上 2000 就是 2000 到 5000 的工资范围。CAST(i as nchar(20))把数字转成字符串拼接到 name 后面。这里有个小坑rand()在同一个批处理中多次调用会返回相同的值因为种子相同。要生成不同的随机工资需要用rand(checksum(newid()))或者每行用abs(checksum(newid())) % 3001。报告里的写法在循环中每次调用 rand()实际上 SQL Server 对 rand() 的处理是每次调用都重新播种所以结果确实是随机的。但如果在单条 SELECT 里对多行调用 rand()就会全部一样。5. 避坑与排查那些实验报告里没写清楚的细节这部分是我自己踩过的坑结合报告里的代码把最容易翻车的地方列出来。5.1 触发器里 inserted 和 deleted 为空的情况现象写了一个 after insert 触发器里面直接select * from inserted插入单条数据没问题批量插入时只处理了最后一条。原因inserted 和 deleted 是临时表可能包含多行。用变量接收时如果 SELECT 返回多行变量只保留最后一行的值。解决要么用游标遍历 inserted要么把逻辑写成基于集合的 UPDATE/INSERT 语句。报告里 CAvgGrade 触发器就犯了这个错只处理了一个课号。5.2 instead of 触发器忘记写实际插入语句现象创建了 instead of insert 触发器执行插入后提示成功但表里没数据。原因instead of 触发器完全替代了原始操作如果不手动写 INSERT数据就不会进表。解决在 else 分支里补上insert into 目标表 select * from inserted。报告里的 insert_s 触发器就有这个问题验证时“成功”的记录其实没进 SC 表。5.3 外键约束阻止 after 触发器执行现象在 S 表上建了 after delete 触发器想级联删除 SC 表记录但删除 S 表记录时报外键冲突。原因after 触发器在主表操作之后执行但外键约束检查发生在触发器之前。如果 SC 表有外键指向 S 表删除 S 表记录会先被外键拦住。解决先删除外键约束或者改用 instead of delete 触发器在触发器内部先删子表再删主表。报告里第一步就删了外键约束这个顺序是对的。5.4 加密存储过程无法修改现象用with encryption创建了存储过程后来想改一个条件执行alter procedure报错。原因加密后的存储过程定义文本不可读ALTER 需要原始文本才能修改。解决只能drop再create。如果逻辑复杂建议先在外部保存一份源码。我一般会在项目里单独放一个procedures.sql文件所有存储过程都留底加密只是部署时加选项。5.5 游标忘记关闭和释放现象反复执行带游标的存储过程几次之后报“游标已存在”或内存占用持续上升。原因游标使用后没有 close 和 deallocate连接没有释放资源。解决在存储过程末尾确保close 游标名和deallocate 游标名都执行。如果中间有 return 或错误跳转要用 TRY-CATCH 保证释放。我习惯在 open 之后立刻写好 close 和 deallocate 的框架再填中间逻辑。6. 进阶技巧用系统视图验证触发器与存储过程的状态实验报告只要求“执行并验证结果”但实际工作中更需要知道“怎么确认它真的生效了”。我一般会查系统视图来验证对象状态这比反复执行测试数据更可靠。查存储过程定义用sys.sql_modulesselect object_name(object_id) as proc_name, definition, is_encrypted from sys.sql_modules where object_id object_id(jsearch)is_encrypted为 1 表示加密definition 为 NULL。这个视图比sp_helptext更底层加密的也能看到加密标记。查触发器状态用sys.triggersselect name, is_disabled, is_instead_of_trigger from sys.triggers where parent_id object_id(S)is_disabled为 1 表示已禁用is_instead_of_trigger为 1 表示是 instead of 类型。禁用和启用触发器后这个视图的状态会实时变化比反复执行 DELETE 语句验证快得多。查依赖关系用sys.sql_expression_dependenciesselect referencing_entity_name, referenced_entity_name from sys.sql_expression_dependencies where referenced_entity_name S改表结构之前先跑这个看看哪些存储过程、触发器、视图引用了它。报告里用sp_rename改视图名没出问题是因为没有其他对象引用它。实际项目里改表名之前不查依赖就是给自己埋雷。最后说一个习惯每次创建触发器之前先查一下同名的存不存在存在就先 drop。我见过太多人反复执行创建语句报“对象已存在”的错误然后花时间排查。用if object_id(trigger_name) is not null drop trigger trigger_name包一层省心很多。从那以后我每次建触发器、存储过程之前都强制走一遍存在性检查再也没在这上面翻过车。希望帮到你。本文还有配套的精品资源点击获取
