tableA (classdate datetime,xh char(3),detail char(1000) null)
tableB (classdate datetime,xh char(3),detail char(1000) null)
tableC (classdate datetime,xh char(3),detail char(1000) null)
classdate,xh为联合主键
1、在tableA上建立触发器(只建立其中一个)
----trigger A-----
create trigger tr_u_table on tableA for update
as
if @@ROWCOUNT = 0 return
update tableB set classdate= i.classdate,xh = i.xh,detail = i.detail
from tableB,deleted as d, Inserted as i
where tableB.classdate = d.classdate and tableB.xh = d.xh
end----trigger B-----
create trigger tr_u_table on tableA for update
as
if @@ROWCOUNT = 0 return
update tableB set classdate= i.classdate,xh = i.xh,detail = i.detail
from tableB,Inserted as i
where tableB.classdate = i.classdate and tableB.xh = i.xh
end
为什么当批量UPDATE tableA 时,使用trigger A是会出现 主键重复 出错
而使用trigger B 却没有问题呢?2、tableA已经建立trigger B触发器,当tableB有一条记录(classdate = '2007-06-01',xh = '12')时,
向tableA使用insert into语句从tableC中select 3条记录插入到tableA
tableC的记录有:
classdate = '2007-06-01',xh = '11'
classdate = '2007-06-01',xh = '12'
classdate = '2007-06-01',xh = '13'
请问tableA的触发器应该怎样写才能把记录xh = 11和xh = 13插入到tableB中呢?
tableB (classdate datetime,xh char(3),detail char(1000) null)
tableC (classdate datetime,xh char(3),detail char(1000) null)
classdate,xh为联合主键
1、在tableA上建立触发器(只建立其中一个)
----trigger A-----
create trigger tr_u_table on tableA for update
as
if @@ROWCOUNT = 0 return
update tableB set classdate= i.classdate,xh = i.xh,detail = i.detail
from tableB,deleted as d, Inserted as i
where tableB.classdate = d.classdate and tableB.xh = d.xh
end----trigger B-----
create trigger tr_u_table on tableA for update
as
if @@ROWCOUNT = 0 return
update tableB set classdate= i.classdate,xh = i.xh,detail = i.detail
from tableB,Inserted as i
where tableB.classdate = i.classdate and tableB.xh = i.xh
end
为什么当批量UPDATE tableA 时,使用trigger A是会出现 主键重复 出错
而使用trigger B 却没有问题呢?2、tableA已经建立trigger B触发器,当tableB有一条记录(classdate = '2007-06-01',xh = '12')时,
向tableA使用insert into语句从tableC中select 3条记录插入到tableA
tableC的记录有:
classdate = '2007-06-01',xh = '11'
classdate = '2007-06-01',xh = '12'
classdate = '2007-06-01',xh = '13'
请问tableA的触发器应该怎样写才能把记录xh = 11和xh = 13插入到tableB中呢?
as
if @@ROWCOUNT = 0 return
update tableB
set
classdate= i.classdate,xh = i.xh,detail = i.detail
from
tableB,
deleted as d,
Inserted as i
where
tableB.classdate = d.classdate
and
tableB.xh = d.xh
and --在执行批量更新时,inserted和deleted中同时存在多条记录
tableB.xh = i.xh --必须指定与inserted的关联,否则会出现笛卡儿集
end
as
begin
insert into tableB(classdate,xh,detail)
select classdate,xh,detail from inserted where not exists(select 1 from tableB where xh=inserted.xh)
end
go