SqlServer和Oracle中一些常用的sql语句8 触发器和事务

--创建和执行事后触发器--更新仓库备份表中记录时自动创建数据表且插入三条记录create trigger db_trigger1 on 仓库备份 for updateasbegin  if Exists(select * from sys.sysobjects  where id=OBJECT_ID('db_tabletriuser'))    drop table db_tabletriuser  else  begin     create table db_tabletriuser(         userid  int identity(1,1) primary key,         username  varchar(50),         userpwd   varchar(50)      )    insert into db_tabletriuser(username,userpwd) values('hy1','111')    insert into db_tabletriuser(username,userpwd) values('hy2','222')    insert into db_tabletriuser(username,userpwd) values('hy3','333')   endend--testupdate 仓库备份 set 面积=面积-100 where 仓库号='wh1'      -------------------------------------------------------------删除功能触发器--实现当删除职工表中的一条记录时,删除该职工所对应的订购单信息Create trigger db_delete_trigger on 职工 for deleteasbegin  declare @t varchar(10)  select @t=职工号 from deleted  delete from 订购单 where 职工号=@tend--testdelete from  职工 where 职工号 ='zg5'---------------------------------------------------------更新功能触发器--实现更新仓库表中一条记录的仓库号时,把职工表中对应的仓库号也进行修改create trigger db_update_trigger on 仓库 for updateasbegin  declare @old char(10)  declare @new char(10)  select @new=仓库号 from inserted  select @old=仓库号 from deleted  update 职工 set 仓库号=@new where 仓库号=@oldend--testupdate 仓库 set 仓库号='modifywh2' where 仓库号='wh2'--------------------------------------------------------------嵌套触发器--实现当删除仓库表中一个仓库信息时,同时删除该仓库中所有职工的信息--同时触发删除该仓库所有职工的订购单信息create trigger db_Text_trigger1 on 仓库 for deleteasbegin  declare @t varchar(10)  select @t=仓库号 from  deleted  delete from 职工 where 仓库号=@tendcreate trigger db_Text_trigger2 on 职工 for deleteasbegin  delete from 订购单 where 职工号 in (select 职工号 from deleted)end--testdelete from 仓库 where 仓库号='wh1'----------------------------------------------------------创建触发器实现在仓库备份表中插入,更新或删除记录时自动发送邮件create trigger db_trigger2 on 仓库备份 for Insert,Update ,Delete      asbegin  execute xp_sendmail '[e-mail]'end   --禁用和启用触发器--disable 禁用;Enable 启用alter table 仓库备份disable trigger db_trigger1alter table 仓库备份Enable trigger db_trigger1alter table 仓库备份disable trigger db_trigger1, db_trigger2alter table 仓库备份Enable trigger all      --保护数据表不被修改和删除的触发器create trigger db_DDL_trigger1on databasefor drop_table,Alter_tableasbegin  print '不能修改和删除当前数据库中的数据表！'  rollbackenddrop table 仓库备份   --保护数据库不被删除的触发器create trigger db_DDL_trigger2on all serverfor drop_databaseasbegin  print '不能删除当前服务器中的任何数据库'  rollbackenddrop database  db_business  --记录数据库操作日志的触发器create table 记录日志表(  日志编号     int identity(1,1) primary key,  事件         varchar(600) null,  所用语句     varchar(8000)  null,  操作者       varchar(60)  null,  发生时间     datetime     null)create trigger db_DDL_trigger3on database for DDL_Database_level_Eventsasbegin   declare  @log xml   set @log =Eventdata()   insert into 记录日志表(事件,所用语句,操作者,发生时间) values   (     @log.value('(/Event_Instance/EventType)[1]','nvarchar(100)'),     @log.value('(/Event_Instance/TSQLCommand)[1]','nvarchar(2000)'),     Convert(nvarchar(100),Current_user),     Getdate()   )end--testcreate table db_table2(  仓库编号  int not null,  仓库号   varchar(50) not null,  城市     varchar(50) not null,  面积     int)select * from 记录日志表------------------------------------------

--事务begin transaction update_datause db_business goupdate 职工备份 set 姓名='事务修改' where 职工号='zg2'rollback transaction update_data --回滚--testselect * from 职工备份--------------------------begin transaction update_datause db_business goupdate 职工备份 set 姓名='事务修改' where 职工号='zg2'commit transaction update_data --提交--testselect * from 职工备份