表格 IPArea 有字段 IPStart varchar(50), IPEnd varchar(50),class int
就是起始IP和终止IP以及权限级别,
起始IP和终止IP的前三个IP段相同,最后一个IP段不同。
另外一个表格 IPDetail 字段 IP varchar(50),class int
将IPArea里面的IP段转化为单个的IP地址。
比如说IPArea加入记录 IPStart :1.1.1.2
IPEnd :1.1.1.4
class: 3
则IPDetail 自动增加3条记录:
IP class
1.1.1.2 3
1.1.1.3 3
1.1.1.4 3
删除的时候也一样,保持两个表格的同步。
请问这个触发器要如何创建,
经理说做不出来就得加班。请大家帮忙。
就是起始IP和终止IP以及权限级别,
起始IP和终止IP的前三个IP段相同,最后一个IP段不同。
另外一个表格 IPDetail 字段 IP varchar(50),class int
将IPArea里面的IP段转化为单个的IP地址。
比如说IPArea加入记录 IPStart :1.1.1.2
IPEnd :1.1.1.4
class: 3
则IPDetail 自动增加3条记录:
IP class
1.1.1.2 3
1.1.1.3 3
1.1.1.4 3
删除的时候也一样,保持两个表格的同步。
请问这个触发器要如何创建,
经理说做不出来就得加班。请大家帮忙。
create table iparea(IPStart varchar(50),IPEnd varchar(50),class int)
create table IPDetail(IP varchar(50),class int)
alter trigger tri_iparea on iparea after insert,update,delete
as
--新增时
if exists(select 1 from inserted) and not exists(select 1 from deleted)
begin
declare @count int
declare @n int
declare @var varchar(50)
select @var=reverse(substring(reverse(IPStart),charindex('.',reverse(IPStart))+1,len(IPStart))) from inserted --前三段
select @n=reverse(substring(reverse(IPStart),1,charindex('.',reverse(IPStart))-1)) from inserted --起始ip的第四段
select @count=reverse(substring(reverse(IPEnd),1,charindex('.',reverse(IPEnd))-1)) from inserted --终止ip的第四段
while(@n<=@count)
begin
insert into IPDetail select @var+'.'+cast(@n as varchar),class from inserted
set @n=@n+1
end
end
--删除时
else if not exists(select 1 from inserted) and exists(select 1 from deleted)
begin
declare @count1 int
declare @n1 int
declare @var1 varchar(50)
select @var1=reverse(substring(reverse(IPStart),charindex('.',reverse(IPStart))+1,len(IPStart))) from deleted --前三段
select @n1=reverse(substring(reverse(IPStart),1,charindex('.',reverse(IPStart))-1)) from deleted --起始ip的第四段
select @count1=reverse(substring(reverse(IPEnd),1,charindex('.',reverse(IPEnd))-1)) from deleted --终止ip的第四段
while(@n1<=@count1)
begin
delete IPDetail from ipdetail a,deleted b where a.IP=@var1+'.'+cast(@n1 as varchar) and a.class=b.class
set @n1=@n1+1
end
end--测试:
insert into iparea select '1.2.3.12','1.2.3.18',5
--查看结果
select * from iparea
select * from IPDetail--测试
delete iparea where class=5
--查看结果
select * from iparea
select * from IPDetail
也就是说:--测试:
insert into iparea select '1.2.3.12','1.2.3.18',5 union all select '1.2.4.2','1.2.4.38',3
--或者一次delete多条纪录
create trigger tri_iparea on iparea after insert,update,delete
asselect top 256 IDENTITY(int,0,1) as id
into # from sysobjects a,sysobjects bdelete a
from ipdetail a,deleted b,# t
where a.class=b.class
and t.id>=cast(PARSENAME(b.IPStart,1) as int)
and t.id<=cast(PARSENAME(b.IPEnd,1) as int)
and a.IP=PARSENAME(b.IPStart,4)+'.'+PARSENAME(b.IPStart,3)+'.'+PARSENAME(b.IPStart,2)+'.'+cast(t.Id as varchar)
insert ipdetail(Ip,Class)
select PARSENAME(b.IPStart,4)+'.'+PARSENAME(b.IPStart,3)+'.'+PARSENAME(b.IPStart,2)+'.'+cast(t.Id as varchar) as Ip,b.Class
from inserted b,# t
where t.id>=cast(PARSENAME(b.IPStart,1) as int)
and t.id<=cast(PARSENAME(b.IPEnd,1) as int)
create table iparea(IPStart varchar(50),IPEnd varchar(50),class int)
go
create table IPDetail(IP varchar(50),class int)
go
create trigger tri_n
on iparea
for insert,update ,delete
asselect top 256 IDENTITY(int,0,1) as id
into # from sysobjects a,sysobjects bdelete a
from ipdetail a,deleted b,# t
where a.class=b.class
and t.id>=cast(PARSENAME(b.IPStart,1) as int)
and t.id<=cast(PARSENAME(b.IPEnd,1) as int)
and a.IP=PARSENAME(b.IPStart,4)+'.'+PARSENAME(b.IPStart,3)+'.'+PARSENAME(b.IPStart,2)+'.'+cast(t.Id as varchar)
insert ipdetail(Ip,Class)
select PARSENAME(b.IPStart,4)+'.'+PARSENAME(b.IPStart,3)+'.'+PARSENAME(b.IPStart,2)+'.'+cast(t.Id as varchar) as Ip,b.Class
from inserted b,# t
where t.id>=cast(PARSENAME(b.IPStart,1) as int)
and t.id<=cast(PARSENAME(b.IPEnd,1) as int)
go
insert iparea select '192.168.0.1','192.168.0.5',3
select * from iparea
select * from ipdetaildelete from iparea
select * from iparea
select * from ipdetail
drop table iparea,IPDetail/*IPStart IPEnd class
-------------------------------------------------- -------------------------------------------------- -----------
192.168.0.1 192.168.0.5 3(所影响的行数为 1 行)IP class
-------------------------------------------------- -----------
192.168.0.1 3
192.168.0.2 3
192.168.0.3 3
192.168.0.4 3
192.168.0.5 3(所影响的行数为 5 行)
(所影响的行数为 256 行)
(所影响的行数为 5 行)
(所影响的行数为 1 行)IPStart IPEnd class
-------------------------------------------------- -------------------------------------------------- ----------- (所影响的行数为 0 行)IP class
-------------------------------------------------- ----------- (所影响的行数为 0 行)
*/