MSSQL触发器

太久没有写过触发器了，今天写了一个给同事，以后再温习。

Ken.Sniper 16:50:45 if exists(select * from sysobjects where name='tr_getdata' and type='TR') drop trigger tr_getdata go create trigger tr_getdata on BATTLE_TEMPORARY with encryption after insert as declare @bID bigint select @bID='ID字段' from inserted if not exists(select * from BATTLE_TEMPORARY where 'ID字段'=@bID) begin raiserror('插入数据失败，触发器无法触发',16,1) rollback transaction end else begin '在此处写入你要做的更新，字段标识ID在上面已经取出。' end go -- use master go --创建数据库 if exists(select * from sysdatabases where name='CityManager') drop database CityManager go create database CityManager on ( name='CityManagerDB', filename='c:CityManagerData.mdf', filegrowth=1mb ) Log on ( name='CityManagerLOG', filename='c:CityManagerLog.ldf', filegrowth=1mb ) go use CityManager go --创建国家表 if exists(select * from sysobjects where name='State') drop table State go create table State ( STID int primary key, --国家编号 STCN varchar(200) not null unique, --国家中文名称 STEN varchar(200) not null unique --国家英文名称 ) go --创建省表 if exists(select * from sysobjects where name='Province') drop table Province go create table Province ( PRID int primary key, --省编号 PRCN varchar(200) not null unique, --省中文名 PREN varchar(200) not null unique, --省英文名 STID varchar(200) not null --国家编号 ) --创建城市表 if exists(select * from sysobjects where name='City') drop table City go create table City ( CIID int primary key, --城市编号 CICN varchar(200) not null UNIQUE, --城市中文名称 CIEN varchar(200) not null unique, --城市英文名称 STID int not null, --国家编号 PRID int not null --省编号 ) go --为State创建delete触发器,级联删除City表中的数据 if exists(select * from sysobjects where type='tr' and name='State_City_Del') drop trigger State_City_Del go create trigger State_City_Del on State for delete as declare @STID int select @STID=STID from deleted delete City where STID=@STID go --为State创建delete触发器,级联删除Province表中的数据 if exists(select * from sysobjects where type='tr' and name='State_Province_Del') drop trigger State_Province_Del go create trigger State_Province_Del on State for delete as declare @STID int select @STID=STID from deleted delete Province where STID=@STID go --为State创建delete触发器,当删除某个国家后,重新排列State表的主键编号 if exists(select * from sysobjects where type='tr' and name='State_UpdateSTID_Del') drop trigger State_UpdateSTID_Del go create trigger State_UpdateSTID_Del on State for delete as declare @STID int select @STID=STID from deleted declare @tempID int set @tempID=@STID+1 while @tempID<=((select count(*) from State)+1) begin update State set STID=@tempID-1 where STID=@tempID set @tempID=@tempId+1 end --为State创建update触发器,更新国家序列号时,级联更新该国家所对应的省Province的STID的值 if exists(select * from sysobjects where type='tr' and name='State_Province_Update') drop trigger State_Province_Update go create trigger State_Province_Update on State for update as declare @STIDB int declare @STIDE int select @STIDB=STID from deleted select @STIDE=STID from inserted update Province set STID=@STIDE where STID=@STIDB --为State创建update触发器,更新国家序列号时,级联更新该国家所对应的城市City的STID的值 if exists(select * from sysobjects where type='tr' and name='State_City_Update') drop trigger State_City_Update go create trigger State_City_Update on State for update as declare @STIDB int declare @STIDE int select @STIDB=STID from deleted select @STIDE=STID from inserted update City set STID=@STIDE where STID=@STIDB --为Province创建insert触发器,如果插入的STID不存在State中,则禁止插入 if exists(select * from sysobjects where type='tr' and name='Province_State_Insert') drop trigger Province_State_Insert go create trigger Province_State_Insert on Province for insert as declare @STID int select @STID=STID from inserted if not exists(select * from State where STID=@STID) begin raiserror('插入失败',16,1) rollback transaction end else print '插入成功' go --为Province创建delete触发器,级联删除City表中的数据 if exists(select * from sysobjects where type='tr' and name='Province_City_Del') drop trigger Province_City_Del go create trigger Province_City_Del on Province for delete as declare @PRID int select @PRID=PRID from deleted delete City where PRID=@PRID go --为Province创建delete触发器,当删除某个省后,重新排列Province表的主键编号 if exists(select * from sysobjects where type='tr' and name='Province_UpdatePRID_Del') drop trigger Province_UpdatePRID_Del go create trigger Province_UpdatePRID_Del on Province for delete as declare Province_UpdatePRID_Cur cursor for select * from Province order by PRID open Province_UpdatePRID_Cur declare @prid int declare @prcn varchar(200) declare @pren varchar(200) declare @stid int declare @newprid int set @newprid=1 fetch next from Province_UpdatePRID_Cur into @prid,@prcn,@pren,@stid while @@fetch_status=0 begin update Province set PRID=@newprid where PRID=@prid set @newprid=@newprid+1 fetch next from Province_UpdatePRID_Cur into @prid,@prcn,@pren,@stid end deallocate Province_UpdatePRID_Cur --为Province创建update触发器,当更新某省的PRID的值的时候,级联更新City表的PRID的值 if exists(select * from sysobjects where type='tr' and name='Province_City_Update') drop trigger Province_City_Update go create trigger Province_City_Update on Province for update as declare @PRIDB int declare @PRIDE int select @PRIDB=PRID from deleted select @PRIDE=PRID from inserted update City set PRID=@PRIDE where PRID=@PRIDB --为City的STID创建insert触发器,如果插入的STID不存在State中,则禁止插入 if exists(select * from sysobjects where type='tr' and name='State_City_Insert') drop trigger State_City_Insert go create trigger State_City_Insert on City for insert as declare @STID int select @STID=STID from inserted if not exists(select * from State where STID=@STID) begin raiserror('插入失败',16,1) rollback transaction end else print '插入成功' go --为City的PRID创建insert触发器,如果插入的PRID不存在Province中,则禁止插入 if exists(select * from sysobjects where type='tr' and name='City_Province_Insert') drop trigger City_Province_Insert go create trigger City_Province_Insert on City for insert as declare @PRID int select @PRID=PRID from inserted if not exists(select * from Province where PRID=@PRID) begin raiserror('插入失败',16,1) rollback transaction end else print '插入成功' go --为City的PRID创建delete触发器,当删除某些城市后,重新排列城市编号 if exists(select * from sysobjects where type='tr' and name='City_UpdateCIID_Del') drop trigger City_UpdateCIID_Del go create trigger City_UpdateCIID_Del on City for delete as declare City_UpdateCIID_Cur cursor for select * from City order by CIID open City_UpdateCIID_Cur declare @ciid int declare @cicn varchar(200) declare @cien varchar(200) declare @stid int declare @prid int declare @newciid int set @newciid=1 fetch next from City_UpdateCIID_Cur into @ciid,@cicn,@cien,@stid,@prid while @@fetch_status=0 begin update City set CIID=@newciid where CIID=@ciid set @newciid=@newciid+1 fetch next from City_UpdateCIID_Cur into @ciid,@cicn,@cien,@stid,@prid end deallocate City_UpdateCIID_Cur --清空表内数据 truncate table State go truncate table City go truncate table Province go --插入测试数据 insert into State values(1,'中国','China') go insert into State values(2,'美国','America') go insert into State values(3,'英国','England') go insert into Province values(1,'北京','BeiJing',1) go insert into Province values(2,'重庆','ChongQing',1) go insert into Province values(3,'四川','SiChuan',1) go insert into Province values(4,'纽约','NewYork',2) go insert into Province values(5,'伦敦','London',3) go insert into City values(1,'重庆','ChongQing',1,2) go insert into City values(2,'成都','ChengDu',1,3) go insert into City values(3,'北京','BeiJing',1,1) go insert into City values(4,'纽约','NewYork',2,4) go insert into City values(5,'伦敦','London',3,5) go --查看表记录 select * from State select * from Province select * from City delete State where STID=1