触发器实例

alter trigger V_DDB
on housebuild
for insert
as
--当楼栋增加的时候，增加到ddb表中
declare @mc varchar(50),@bh varchar(50)
select @mc=mc,@bh=bh from inserted
--将楼栋编号补齐6位
set @bh=right('000000'+CAST(@bh as varchar(50)),6)
insert into Ddb(ddbh,mc,sjdd,ddzt,ddh)select @bh,@mc,'000001','1',@bh inserted
go
select * from ddb
select * from HouseBuild

 

*******************

alter trigger update_ddB
on housebuild
for update
as
declare @oldmc varchar(50)
declare @newbh varchar(50)
declare @newmc varchar(50)
declare @oldbh varchar(50)
--更新前的数据
select @oldmc=mc from deleted
if(exists(select * from Ddb where mc like '%'+@oldmc+'%'))
begin
select @newmc=mc from inserted
--set @bh = RIGHT('000000'+CAST(@bh as varchar(50)),6)
update Ddb set mc=@newmc where mc like '%'+@oldmc+'%'
end
--更新前的数据
select @oldbh=bh from deleted
if(exists(select * from Ddb where ddbh like '%'+@oldbh+'%'))
begin
select @newbh=bh from inserted
update Ddb set ddbh=@newbh,ddh=@newbh where ddbh like '%'+@oldbh+'%'
end

 

转载于:https://www.cnblogs.com/guojie-guojie/p/8342522.html