程序博客网 > 淘宝的处方药是真的吗
来源:互联网 发布:淘宝的处方药是真的吗 编辑:程序博客网 时间:2024/05/17 04:57
/*触发器的使用*/1、触发器的定义: 对某一个表的一定的操作,触发某种条件,从而执行的一段程序, 触发器是一种特殊的存储过程,它不能被显式地调用,而是在往表中插入记录、 更改记录或者删除记录时,当事件发生时,才被自动地激活。2、事务范围触发器和引发触发器执行的命令被当作一次事务处理,因此就具备了事务的所有特征。3、触发器原理 在SQL Server为每个触发器都创建了两个专用表:inserted表和deleted表。 这是两个逻辑表,由系统来维护,在触发执行时存在,在触发结束时消失。 3.1 deleted表存放由于执行delete或update语句而要从表中删除的所有行。 在执行delete或update操作时,被删除的行从激活触发器的表中被移动(move)到deleted表,这两个表不会有共同的行。 3.2 inserted表存放由于执行insert或update语句而要向表中插入的所有行。 在执行insert或update事物时,新的行同时添加到激活触发器的表中和inserted表中, inserted表的内容是激活触发器的表中新行的拷贝。4、触发器类型:instead of 和 after 触发器 主要包括定义和应用范围条件,操作执行时机; 4.1 after触发器:包括update、insert、delete三种 4.2 instead of触发器instead of 触发器主要是用于视图进行数据库的更新操作的,可以利用此触发哭来创建只读视图5、触发器作用: 触发器可以用来对表实施复杂的完整性约束,保持数据的一致性;比如银行转账收支保持平衡等。--insert 触发器if object_id('temp','U') is not nulldrop table tempgocreate table temp(id int not null primary key identity(1,1),pno varchar(50))goif object_id('tri_temp$pno','TR') is not nulldrop trigger tri_temp$pnogocreate trigger tri_temp$pno on tempafter insertasdeclare @pno varchar(50)select @pno=pno from insertedif(left(@pno,4)!='2009')beginrollback transactionraiserror('%s编号格式不正确!以2009开头',16,10,@pno)endgoinsert into temp(pno) values('xxx')insert into temp(pno) values('2009***')select * from tempif object_id('tri_temp$pno','TR') is not nulldrop trigger tri_temp$pnogocreate trigger tri_temp$pno on tempfor insert asdeclare @pno varchar(50)select @pno=pno from insertedif(left(@pno,4)!='2009')beginrollback transactionraiserror('哈哈!,%s编号格式不正确!以2009开头',16,1,@pno)endgoinsert into temp(pno) values('yyy')--2、delete触发器--限制每次只能删除一条记录,防止误操作将数据全部删除if object_id('tri_delete$temp','TR') is not nulldrop trigger tri_delete$tempgocreate trigger tri_delete$temp on tempfor deleteasdeclare @rowcount intselect @rowcount=@@rowcountif @rowcount >1begin rollback transaction raiserror ('当前删除的记录为%d,一次只允许删除一行记录!', 16, 1, @rowcount)endgoinsert into temp(pno) values('2009aaaa')insert into temp(pno) values('2009bbbb')delete from tempselect * from temp--另外一种写法if object_id('tri_delete$temp','tr') is not nulldrop trigger tri_delete$tempgocreate trigger tri_delete$temp on tempafter deleteasdeclare @rowcount intselect @rowcount=@@rowcountif @rowcount >1begin rollback transaction raiserror ('当前删除的记录为%d,一次只允许删除一行记录!', 16, 1, @rowcount)endgodelete from tempselect * from temp--某此记录不能删除if object_id('tri_delete$temp','tr') is not nulldrop trigger tri_delete$tempgocreate trigger tri_delete$temp on tempfor delete as declare @pno varchar(50)select @pno=pno from deletedif (@pno='2009***')beginrollback transactionraiserror('有此记录不能删除,如%s!',16,1,@pno)endgodelete from tempselect * from tempdelete from temp where id=9--3、update触发器if db_id('accp') is nullcreate database accpgouse accpif object_id('bank','u') is not nulldrop table bankgocreate table bank(id int not null primary key identity(1,1),userName varchar(50) not null,balance decimal(7,2) not null,transDate datetime not null)goinsert into bank(userName,balance,transDate) values('张三',38000.68,getdate())insert into bank(userName,balance,transDate) values('李四',800.88,getdate())select * from bank--单次交易金额不允许超过500元if object_id('tri_update$bank','tr') is not nulldrop trigger tri_update$bankgocreate trigger tri_update$bank on bankfor updateasdeclare @beforemoney decimal(7,2),@aftermoney decimal(7,2)select @beforemoney=balance from deletedselect @aftermoney=balance from insertedif abs(@aftermoney-@beforemoney)>500beginprint '交易金额:'+convert(varchar(10), abs (@aftermoney-@beforemoney))raiserror ('每门交易不能超过500元,交易失败',16,1)rollback transactionendgoupdate bank set balance=800 where userName='李四'select * from bank--日期不能修改if object_id('tri_update$bank','tr') is not nulldrop trigger tri_update$bankgocreate trigger tri_update$bank on bankfor updateasif update(transDate)beginprint '交易失败!'raiserror('交易日期是由系统自动产生,不允许手工更改!',16,2)rollback transactionendgoupdate bank set transDate='2012-8-8'select * from bank--4、instead of 触发器--创建只读视图if object_id('v_bank','v') is not nulldrop view v_bankgocreate view v_bank(用户姓名,余额,交易日期)asselect userName,balance,transDate from bankgoif object_id('tri_view$bank','tr') is not nulldrop trigger tri_view$bankgocreate trigger tri_view$bank on bankinstead of insert,update,deleteasprint '视图只读,禁止更新!'goinsert into v_bank values('赵七',200,getdate())select * from v_bankinsert into v_bank values('老九',200,getdate())--多表数据视图更新操作/*1、三张表t1,t2,t3,列完全一样tid,name,date2、创建一个视图是三张表的合集3、向视图中插入数据,根据编号的不同自动将数据插入相关表中*/--创建表if object_id('t1','u') is not nulldrop table t1gocreate table t1(tid varchar(10) not null,name varchar(50) not null,date datetime not null)goif object_id('t2','u') is not nulldrop table t2goif object_id('t3','u') is not nulldrop table t3goselect * into t2 from t1select * into t3 from t1--创建视图if object_id('v_temp','v') is not nulldrop view v_tempgocreate view v_tempas select * from t1 union allselect * from t2 union allselect * from t3go--创建触发器if object_id('tri_t','tr') is not nulldrop trigger tri_tgocreate trigger tri_t on v_tempinstead of insertasdeclare @tempid varchar(10)select @tempid=tid from insertedif substring(@tempid,1,2)='t1' begininsert into t1 select * from insertedreturn endif substring(@tempid,1,2)='t2'begininsert into t2 select * from insertedreturnend if substring(@tempid,1,2)='t3'begininsert into t3 select * from insertedreturn endelsebeginprint '编号格式不正确'rollback transactionendgo--测试insert into v_temp(tid,name,date) values('t1xxx','张三',getdate())insert into v_temp(tid,name,date) values('t2xxx','李四',getdate())insert into v_temp(tid,name,date) values('t3xxx','王五',getdate())insert into v_temp(tid,name,date) values('t4xxx','赵六',getdate())select * from t1select * from t2select * from t3