单元11 自动业务处理.ppt

1、单元11,自动业务处理,回顾,使用T-SQL添加约束的语法是： ALTER TABLE 表名ADD CONSTRAINT 约束名 约束类型 约束说明 SQL Server中有6种约束：非空约束（NOT NULL)、主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、检查约束（CHECK）、默认约束（DEFAULT）、外键约束（FOREIGN KEY）。 所有控件都有Validating事件，用它可进行数据验证。设置控件的输入无效：e.Cancel = true； ErrorProvider控件使用图标和提示向用户发出错误通知。 errorProvider1.SetError(txtN

2、ame, 姓名为必填项！); 可以使用TryParse方法、IsXxx函数进行类型验证 遍历窗体每个控件： foreach( Control control in Controls ) DateTimePicker控件的主要属性有：Value获取所选择的日期/时）、Text获取控件显示的文本、Value.Date获取当前控件选取的短日期。 MaskEdit控件的主要属性有MaskFormt。,本单元任务,任务一 增删学生时自动更新所属班级人数 任务二 审计“学生更换班级”,新增管理员,新增教师,新增学生,本单元目标,掌握如何使用inserted和deleted表 掌握创建after触发器的方法

3、 掌握创建Instead of 触发器的方法 掌握创建DDL 触发器的方法 掌握禁用/启用触发器,任务一 完成“新增管理员”模块,任务描述：,任务分析：,周丽加强了学生信息系统的测试，当他测试增加学生模块时，发现增添学生信息没有问题，但学生增加后，班级人数没有随之加一。周丽提交测试记录后，大宝经理指出，这是相关表的数据一致性问题，必须整改。,班级人数是为了加快查询而设置的冗余字段，它应与学生表中的数据相符合。要解决该问题，可以修改所有学生信息维护模块的保存代码，包括“新增学生”、“学生换班级”、“删除学生”等，除了更新学生表时，还必须更新班级人数，这样又涉及多条SQL语句，需要增加事务控制。如

4、果修改代码来解决该问题，代码维护的工作量非常巨大；另外，也不能保证开发员用其他方式增加学生信息。 要从根本改变相关表数据不一致的情况，可以使用触发器。,赵二,更新,触发器,插入,触发器触发,新生注册,学生表,班级表,触发器,触发器是在对表进行插入、更新或删除操作时自动执行的存储过程。 触发器的优点： 相关表自动进行级联更改 跟踪变化 强化约束,触发器的类别,触发器,DML触发器,DDL触发器,SQL Server 2005触发器类别：,AFTER,INSTEAD OF,服务器,数据库,触发器触发时： 系统自动在内存中创建deleted表或inserted表 只读，不允许修改；触发器执行完成后，

5、自动删除 inserted 表 临时保存了插入或更新后的记录行 可以从inserted表中检查插入的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作 deleted 表 临时保存了删除或更新前的记录行 可以从deleted表中检查被删除的数据是否满足业务需求 如果不满足，则向用户报告错误消息，并回滚插入操作,inserted 和deleted 表,inserted 和deleted 表,inserted表和deleted表存放的信息,创建DML触发器,语法,CREATE TRIGGER 触发器名 ON 作用域 AFTER, INSTEAD OF 触发事件 AS BEGIN

6、 触发器主体：SQL语句 END,创建After Insert触发器,语法 【例11-1】增加学生在打印添加一位学生消息，以及时间。,CREATE TRIGGER 触发器名 ON 作用域 AFTER, INSTEAD OF 触发事件 AS BEGIN 触发器主体：SQL语句 END,CREATE TRIGGER t_student_Insert ON Student AFTER INSERT AS BEGIN -Convert(., 20) 的作用是将当前时间转换为yyyy-mm-dd hh:mi:ss(24h) print 添加一位学生 + Convert(varchar(16),GetDa

7、te(), 20) END GO,创建After Delete触发器,【例11-2】实现禁止删除学生数据，即不允许删除学生信息。,CREATE TRIGGER t_student_delete ON Student AFTER DELETE AS BEGIN print 不能删除学生信息 rollback tran END GO,验证 ： delete from student where studentid=100,创建After Update触发器,【例11-3】实现下列规定：周末不允许修改学生信息（无论增加、删除还是修改）,CREATE TRIGGER t_student_update_

8、weekEnd ON Student AFTER UPDATE, INSERT, DELETE AS BEGIN if (DatePart(dw, GetDate() in (1, 7) Begin print 不能在周末修改数据！ rollback end END GO,验证 ： delete from student where studentid=100,创建Instead of触发器,Instead Of触发器可以同时在数据表和视图中使用 【例11-4】不允许删除学生信息,CREATE TRIGGER t_student_delete1 ON Student Instead Of DE

9、LETE AS BEGIN print 不能删除学生信息 END GO,验证 ： 删除触发器t_student_delete后，仍然不能删除学生信息,创建DDL触发器,语法,CREATE TRIGGER 触发器名 ON ALL SERVER或DATABASE AFTER 激活DDL触发器的事件 AS BEGIN 触发器主体：SQL语句 END,创建数据级触发器,【例11-5】保护数据库中的数据表不被修改，不被删除。,CREATE TRIGGER t_disable_alterTable ON DATABASE AFTER DROP_TABLE, ALTER_TABLE AS BEGIN PRI

10、NT 对不起，您不能对数据表进行操作! ROLLBACK ; END GO,创建服务器级触发器,【例11-6】监查创建登录、修改登录的修改登录的情况，并打印消息。,CREATE TRIGGER ddl_trig_login ON ALL SERVER AFTER DDL_LOGIN_EVENTS AS PRINT 登录相关事件发生！ SELECT EVENTDATA().value(/EVENT_INSTANCE/TSQLCommand/CommandText)1,nvarchar(max) GO,管理触发器,查看触发器 设置After触发器的激活顺序 禁用/启用After触发器,设置Afte

11、r触发器的激活顺序,【例11-6】监查创建登录、修改登录的修改登录的情况，并打印消息。,sp_settriggerorder 触发器名 , 激活次序 , SQL类别 激活次序：可以为First、Last和None。 SQL类别：可为Insert、Update和Delete。,Exec sp_settriggerorder t_student_Insert2,First,Insert Exec sp_settriggerorder t_student_Insert ,Last,Insert,禁用/启用触发器,语法,Alter table 数据表名 Disable,Enable trigger 触

12、发器名,ALL,Alter table student Disable trigger t_student_update_weekEnd 加班中 Alter table student enable trigger t_student_update_weekEnd,任务实施,1创建触发器 2只生效刚创建的触发器 3初始化班级人数 4验证触发器,创建触发器,关键语法,create trigger t_setudent_change on student after insert,delete,update -需要考虑全面as begin set nocount on-不显示T-SQL 语句影响的

13、行数的消息。 if update(classid)-老生换班级或增加学生 update class set num=num+1-对应班级的人数+1 from inserted where class.classid = inserted.classid update class -删除或换班级时，原班级的人数-1 set num=num-1 from deleted where class.classid = deleted.classid end,任务二 完成“修改密码”模块,任务描述：,任务分析：,教务处领导强调：对学生更换班级一定要严格监控起来，即使是学院内部、专业内部调整班级，教务处也

14、需要掌控这些信息：谁做的修改、什么时间做的、更换前的班级、更换后的班级等。,在系统的原有设计中，专业内更换班级，由管理人员直接修改学生所在班级即可。现在，需要对班级修改这些敏感业务监控起来， 1可创建After Update触发器来监控数据的变化。 2只希望对班级列审计，可使用UPDATE（列名）来判断是否修改班级号的数据。 3原班级号、新班级号信息分别从Deleted、Inserted表中获取。 4修改发生时间可从GETDATE()获取。,相关知识与技能,1获取系统数据 2测试对指定列的UPDATE或INSERT操作,SUSER_SNAME（ 用户号 ）：返回与安全标识号（SID）关联的登录

15、名。 HOST_NAME ()：返回工作站名。,UPDATE（列名）：测试对某个列的UPDATE或INSERT尝试，返回真或假。 COLUMNS_UPDATED：可以测试对多个列执行的UPDATE或INSERT操作，并返回一个位模式，指示插入或更新的列。,任务实施,1创建审计表 2创建触发器 3验证该触发器,UserHelper类,创建审计表,CREATE TABLE tAudit( ID int IDENTITY(1,1) NOT NULL Primary Key, TableName nvarchar(50) NOT NULL, -被审计的表名 KeyValue varchar(50) N

16、OT NULL, -表的主键名值 FieldNamenvarchar(50) NOT NULL,-审计的字段名 OldValue nvarchar(500) NULL,-修改前数据 NewValue nvarchar(500) NULL, -修改后数据 update_uservarchar(40)default SUSER_SNAME(),-记录修改者的登录号 update_pcvarchar(40)default HOST_NAME(),-记录修改者的客户机名 update_timedatetimedefault GETDATE()-记录修改发生时间 ),创建触发器,CREATE TRIGG

17、ER tr_audit_student_classid ON student AFTER UPDATE AS BEGIN SET NOCOUNT ON if update(ClassID) begin insert into tAudit (TableName, KeyValue , FieldName , OldValue , NewValue) SELECT 学生表, del.studentNo, classid, del.classid, ins.classid FROM DELETED del, INSERTED ins Where del.studentNo=in.studentNo. End END GO,update student set classid=3 where studentno=00000998,总结,触发器是自动激活的存贮过程，通常用于实现自动业务处理。 触发器与引起触发的SQL同在一个