10 触发器

/* 触发器的使用 */ 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 null drop table temp go create table temp ( id int not null primary key identity(1,1), pno varchar(50) ) go if object_id('tri_temp$pno','TR') is not null drop trigger tri_temp$pno go create trigger tri_temp$pno on temp after insert as declare @pno varchar(50) select @pno=pno from inserted if(left(@pno,4)!='2009') begin rollback transaction raiserror('%s编号格式不正确！以2009开头',16,10,@pno) end go insert into temp(pno) values('xxx') insert into temp(pno) values('2009***') select * from temp if object_id('tri_temp$pno','TR') is not null drop trigger tri_temp$pno go create trigger tri_temp$pno on temp for insert as declare @pno varchar(50) select @pno=pno from inserted if(left(@pno,4)!='2009') begin rollback transaction raiserror('哈哈！，%s编号格式不正确！以2009开头',16,1,@pno) end go insert into temp(pno) values('yyy') --2、delete触发器 --限制每次只能删除一条记录，防止误操作将数据全部删除 if object_id('tri_delete$temp','TR') is not null drop trigger tri_delete$temp go create trigger tri_delete$temp on temp for delete as declare @rowcount int select @rowcount=@@rowcount if @rowcount >1 begin rollback transaction raiserror ('当前删除的记录为%d，一次只允许删除一行记录！', 16, 1, @rowcount) end go insert into temp(pno) values('2009aaaa') insert into temp(pno) values('2009bbbb') delete from temp select * from temp --另外一种写法 if object_id('tri_delete$temp','tr') is not null drop trigger tri_delete$temp go create trigger tri_delete$temp on temp after delete as declare @rowcount int select @rowcount=@@rowcount if @rowcount >1 begin rollback transaction raiserror ('当前删除的记录为%d，一次只允许删除一行记录！', 16, 1, @rowcount) end go delete from temp select * from temp --某此记录不能删除 if object_id('tri_delete$temp','tr') is not null drop trigger tri_delete$temp go create trigger tri_delete$temp on temp for delete as declare @pno varchar(50) select @pno=pno from deleted if (@pno='2009***') begin rollback transaction raiserror('有此记录不能删除，如%s！',16,1,@pno) end go delete from temp select * from temp delete from temp where id=9 --3、update触发器 if db_id('accp') is null create database accp go use accp if object_id('bank','u') is not null drop table bank go create 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 ) go insert 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 null drop trigger tri_update$bank go create trigger tri_update$bank on bank for update as declare @beforemoney decimal(7,2),@aftermoney decimal(7,2) select @beforemoney=balance from deleted select @aftermoney=balance from inserted if abs(@aftermoney-@beforemoney)>500 begin print '交易金额:'+convert(varchar(10), abs (@aftermoney-@beforemoney)) raiserror ('每门交易不能超过500元，交易失败',16,1) rollback transaction end go update bank set balance=800 where userName='李四' select * from bank --日期不能修改 if object_id('tri_update$bank','tr') is not null drop trigger tri_update$bank go create trigger tri_update$bank on bank for update as if update(transDate) begin print '交易失败！' raiserror('交易日期是由系统自动产生，不允许手工更改！',16,2) rollback transaction end go update bank set transDate='2012-8-8' select * from bank --4、instead of 触发器 --创建只读视图 if object_id('v_bank','v') is not null drop view v_bank go create view v_bank(用户姓名,余额,交易日期) as select userName,balance,transDate from bank go if object_id('tri_view$bank','tr') is not null drop trigger tri_view$bank go create trigger tri_view$bank on bank instead of insert,update,delete as print '视图只读，禁止更新！' go insert into v_bank values('赵七',200,getdate()) select * from v_bank insert into v_bank values('老九',200,getdate()) --多表数据视图更新操作 /* 1、三张表t1,t2,t3，列完全一样tid,name,date 2、创建一个视图是三张表的合集 3、向视图中插入数据，根据编号的不同自动将数据插入相关表中 */ --创建表 if object_id('t1','u') is not null drop table t1 go create table t1 ( tid varchar(10) not null, name varchar(50) not null, date datetime not null ) go if object_id('t2','u') is not null drop table t2 go if object_id('t3','u') is not null drop table t3 go select * into t2 from t1 select * into t3 from t1 --创建视图 if object_id('v_temp','v') is not null drop view v_temp go create view v_temp as select * from t1 union all select * from t2 union all select * from t3 go --创建触发器 if object_id('tri_t','tr') is not null drop trigger tri_t go create trigger tri_t on v_temp instead of insert as declare @tempid varchar(10) select @tempid=tid from inserted if substring(@tempid,1,2)='t1' begin insert into t1 select * from inserted return end if substring(@tempid,1,2)='t2' begin insert into t2 select * from inserted return end if substring(@tempid,1,2)='t3' begin insert into t3 select * from inserted return end else begin print '编号格式不正确' rollback transaction end go --测试 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 t1 select * from t2 select * from t3