create trigger upxx on xx
for insert ,update
as
if update(zd1)
begin update xx set zd2=left(m.zd1,4),
from xx inner join inserted d on krs.id=d.id inner join k0808bm m on xx.bm_no=m.bm_no
end上面的 k0808bm 这个中间的日期是按要当前日期改的,意思就是说我在0809月时,他对应更新的表应该是 k0809bm 谢谢!!!!
for insert ,update
as
if update(zd1)
begin update xx set zd2=left(m.zd1,4),
from xx inner join inserted d on krs.id=d.id inner join k0808bm m on xx.bm_no=m.bm_no
end上面的 k0808bm 这个中间的日期是按要当前日期改的,意思就是说我在0809月时,他对应更新的表应该是 k0809bm 谢谢!!!!
for insert ,update
as
if update(zd1)
begindeclare @sqltext nvarchar(max)set @sqltext = 'update xx set zd2=left(m.zd1,4) from xx inner join inserted d on krs.id=d.id inner join k'
+ right(convert(nvarchar, getdate(), 112), 4) + 'bm m on xx.bm_no=m.bm_no'exec(@sqltext)end
go
insert into k0707bm(bm_no,bm_name)
select '01','xx' union all
select '02','xxx' go
create table k0708bm(bm_no varchar(10),bm_name varchar(10))
go
insert into k0708bm(bm_no,bm_name)
select '01','xx' union all
select '02','xxx' union all
select '03','xxxx'
go
create table xx (zd1 varchar(10),zd2 varchar(10),bm_no varchar(10))
create trigger upxx on xx
for insert ,update
as
if update(zd1)
begin update xx set zd2=m.bm_name
from xx inner join inserted d on xx.id=d.id inner join k0808bm m on xx.bm_no=m.bm_no
end 上面的 k0808bm 这个中间的日期是按要当前日期改的,意思就是说我在0809月时,他对应更新的表应该是 k0809bm 谢谢!!!! 用 exec() @sql 都不行
for insert ,update
as
declasr @tmp1 varchar(1000)
declare @sqlstr varchar(1000)
set @tmp1='K'
if len(datepart(m,getdate()))=1
set @tmp1=@tmp1+'0'set @tmp1=@tmp1+datepart(m,getdate())if len(datepart(d,getdate()))=1
set @tmp1=@tmp1+'0'set @tmp1=@tmp1+datepart(d,getdate())if update(zd1)
begin set @sqlstr='update xx set zd2=m.bm_name '
set @sqlstr=@sqlstr+' from xx inner join inserted d on xx.id=d.id inner join '
set @sqlstr=@sqlstr+@tmp1+' m on xx.bm_no=m.bm_no 'exec(@sqlstr)end
create trigger upxx on xx
for insert ,update
as
declasr @tmp1 varchar(1000)
declare @sqlstr varchar(1000)
--组合表名变量
set @tmp1='k'
if len(datepart(m,getdate()))=1
set @tmp1=@tmp1+'0'set @tmp1=@tmp1+datepart(m,getdate())if len(datepart(d,getdate()))=1
set @tmp1=@tmp1+'0'set @tmp1=@tmp1+datepart(d,getdate())+'bm'if update(zd1)
begin set @sqlstr='update xx set zd2=m.bm_name '
set @sqlstr=@sqlstr+' from xx inner join inserted d on xx.id=d.id inner join '
set @sqlstr=@sqlstr+@tmp1+' m on xx.bm_no=m.bm_no 'exec(@sqlstr)end
for insert
as
begin
declare @tb varchar(100)
set @tb = 'k' + right(convert(varchar(8),getdate(),112),4) + 'bm'
select * into # from inserted
exec('select * into ' + @tb + ' from #')
end
以上为示意写法,只是说明,如何把一inserted的数据放到一个可变名的表中, 具体的逻辑你自己再处理一下就可以了