--select * from HouseFwdanb

--select * from Ddb

alter trigger insert_housefwdanb_ddb

on housefwdanb

after insert

as

begin

declare @fjbh varchar(50) --房间编号

declare @mc varchar(100) --名称

declare @ddzt varchar(2) --地点状态

declare @sjdd varchar(50) --上级地点

declare @ldid varchar(255) --楼栋id

declare @sffh varchar(2) --是否复核

declare @ddh varchar(50)

--定义游标

declare insert_housefwdanb_ddb_cursor scroll cursor

for

select fjbh,mc,ldid,sffh from inserted order by fjbh

open insert_housefwdanb_ddb_cursor--打开游标

fetch next from insert_housefwdanb_ddb_cursor into @fjbh,@mc,@ldid,@sffh

while @@FETCH_STATUS=0

begin

if @sffh=1--已复核

begin

set @ddzt='1'--地点正常

if LEN(@fjbh)>=6--如果房间编号大于6，自动截取

begin

select @fjbh=RIGHT(@fjbh,6)

select @ddh=@fjbh--将截取的房间编号复制给地点号

end

else--如果房间编号小于6位，补齐

begin

select @fjbh=RIGHT('000000'+CAST(@fjbh as varchar),6)--补齐

select @ddh=@fjbh--将补齐的房间编号复制给地点号

end

insert into Ddb(ddbh,mc,sjdd,ddzt,ddh)values(@fjbh,@mc,'000001',@ddzt,@ddh)

end

fetch next from insert_housefwdanb_ddb_cursor into @fjbh,@mc,@ldid,@sffh

end

close insert_housefwdanb_ddb_cursor --关闭游标

end