第20讲 事务与锁实验步骤.doc

-【第20讲 事务与锁】-【显示事务】-【回滚事务】begin transactioninsert into S values(lily,女,18,软件工程0914班)insert into S values(lucy,女,18,软件工程0914班)rollback transaction -回滚事务-验证 select * from S-【提交事务】begin transactioninsert into S values(lily,女,18,软件工程0914班)insert into S values(lucy,女,18,软件工程0914班)commit transaction -提交事务(可省略)-验证 select * from S-begin transactioninsert into S values(lucy2,女,18,软件工程0914班)insert into S values(lucy3,女,18,软件工程0914班)rollback transaction -回滚事务到begin处，此事务已结束commit transaction -由于没有配对的begin transaction，所以报错-验证 select * from S-【回滚事务到保存点】begin transactioninsert into S values(lucy4,女,18,软件工程0914班)insert into S values(lucy5,女,18,软件工程0914班)save transaction point1 -设置保存点point1insert into S values(lucy6,女,18,软件工程0914班)insert into S values(lucy7,女,18,软件工程0914班)rollback transaction point1 -回滚事务到保存点point1处，此事务还未结束commit transaction -提交事务-验证 select * from S-【总结-显示事务的使用】：- begin transaction . rollback transaction 表示事务中的操作又还原到操作前的状态；- begin transaction . commit transaction 表示事务中的操作执行成功；- begin transaction 必须和 rollback transaction 或者 commit transaction配对使用，否则表示事务未结束；- 以上三个关键语句不可同时在一个事务中使用。除非事务中设置有保存点，rollback transaction回滚到保存点处-【隐式事务】-set implicit_transactions on -开启隐式事务insert into S values(Tom,男,18,软件工程0914班)insert into S values(John,男,18,软件工程0914班)rollback transactioninsert into S values(Alice,女,18,软件工程0914班)insert into S values(Mary,女,18,软件工程0914班)commit transactionset implicit_transactions off -关闭隐式事务-【总结-隐式事务的使用】：。-【银行转账事务】-create table account(accountID varchar(20), -账号accountBalance money, -账户余额)insert into account values(11,10000)insert into account values(22,1000)-create proc pro_turnAccountaccountID1 varchar(20), -本账户accountID2 varchar(20), -转账的账户turnAccount money -转账的金额asbeginbegin transactionupdate account set accountBalance = accountBalance - turnAccount where accountID = accountID1update account set accountBalance = accountBalance + turnAccount where accountID = accountID2if (select accountBalance from account where accountID = accountID1) 60beginprint 班级人数不得大于60rollback -回滚endelseprint 添加成功endgo-验证insert into S values(Michael,男,19,软件工程0914班)-写法二：alter trigger T_insert on Sinstead of insertasbegindeclare count int -班级人数select count = count(*) from S where className = (select className from inserted)if count 60beginprint 班级人数不得大于60rollback -回滚endelsebegininsert into S(Sname,Ssex,Sage,ClassName) select Sname,Ssex,Sage,ClassName from insertedprint 添加成功endendgo-验证insert into S values(Michael,男,19,软件工程0914班)- 【上次作业】 -6.模拟一张表完成提款机功能：-1) 创建一个触发器，对ATM表中写入内容时判断该用户帐户是否是合法的，是则成功写入输出“成功完成”,否则提示错误信息“用户名不正确”。create trigger T_06_01 on ATMfor insertasbeginif exists(select * from userInfo where uid = (select uid from inserted)print 成功完成elsebeginprint 用户名不正确rollbackendendgo-验证insert into ATM values(1001,400)-2) 创建一个触发器，当向ATM表中写入内容即用户取款时自动更新他的余额。（提示：当余额小于取款金额时提示报错信息余额不足，并取消当有操作，否则成功取款）。create trigger T_06_02 on ATMfor insertasbegindeclare getmoney money,uid varchar(10),savemoney moneyselect getmoney = getmoney,uid = uid from insertedselect savemoney = savemoney from userInfo where uid = uidif getmoney savemoneybeginprint 余额不足，操作无法执行rollbackendelseupdate userInfo set savemoney = savemoney - getmoney where uid = uiddeclare a moneyselect a = savemoney from userInfo where uid = uidprint 当前余额为： + cast(a as varchar(10)endgo-验证insert into ATM values(1002,1000)-3) 创建一个存储过程，修改userInfo表，添加一个字段flag (int型)。利用update命令直接批量更新flag值，当金额小于值为，否则为。alter table userInfoadd flag int-create proc pro_06_03asbeginupdate userInfo set flag = (casewhen savemoney 100 then 0else 1end)end-exec pro_06_03select * from userInfo-4）创建一个触发器，当userInfo表中金额发生变化时，状态列会自动更新为相应状态。create trigger T_06_04 on userInfo