create trigger tri on A表 for insert as insert B表 select id,name from inserted i where not exists(select 1 from B表 where id=i.id and name=i.name) go
create trigger trigger_name on A for insert as insert B select * from inserted i left join B on i.KeyField=B.KeyField where B.KeyField is null go
实在不好意思,我的问题没有说得很清楚,A表和B表结构不一样,我的触发器如下: ALTER TRIGGER B ON A FOR INSERT AS BEGIN Declare @StateID int Declare @SNumber Nvarchar(20) Select @SNumber = SNumber From Inserted Select @StateID = SuperviseStateID From if @StateID=1 Begin Set @Subject = '描述语句'+@SNumber End else if @StateID=2 Begin Set @Subject = '描述语句'+@SNumber End if @StateID>0 Begin INSERT INTO B ([Subject], AfficheContent, AddUserID) SELECT @Subject, @AfficheContent, @MsgToUserID FROM Inserted End END
create table ta(c1 int,c2 int) create table tb(c1 int,c2 int) go create trigger tri on ta for insert as insert tb select * from inserted goinsert ta select 1,2 union select 3,4 union select 5,6select * from tb /* c1 c2 ----------- ----------- 5 6 3 4 1 2(3 row(s) affected) */ drop table ta,tb
ALTER TRIGGER B ON A FOR INSERT AS BEGIN INSERT INTO B ([Subject], AfficheContent, AddUserID) select case SuperviseStateID when 1 then '描述语句1'+SNumber when 2 then '描述语句2'+SNumber end,AfficheContent,MsgToUserID from inserted where SuperviseStateID>0 END
给你剖析一下你的触发器:ALTER TRIGGER B ON A FOR INSERT AS BEGIN Declare @StateID int Declare @SNumber Nvarchar(20) Select @SNumber = SNumber From Inserted --你这里实际上是选了Inserted集合中的第一条记录 Select @StateID = SuperviseStateID From Inserted --同上if @StateID=1 Begin Set @Subject = '描述语句'+@SNumber End else if @StateID=2 Begin Set @Subject = '描述语句'+@SNumber End if @StateID>0 Begin INSERT INTO B ([Subject], AfficheContent, AddUserID) SELECT @Subject, @AfficheContent, @MsgToUserID --这3个参数经过上面的计算整合,变为3个值,这3个值都是由第一条记录产生的 FROM Inserted --这里的Inserted表显得没有任何意义,因为选出来的3个参数都不是从它里面出来的。它的唯一作用是把那3个定值重复了10遍。 End END
Select @SNumber = SNumber From Inserted ------------------------------------ 这种写法如果是批量更新的时候@SNumber永远是第一条数据,所以要用连接来做
as
insert B表
select id,name from inserted i
where not exists(select 1 from B表 where id=i.id and name=i.name)
go
create trigger trigger_name on A for insert
as
insert B
select * from inserted i left join B on i.KeyField=B.KeyField
where B.KeyField is null
go
ALTER TRIGGER B
ON A
FOR INSERT
AS
BEGIN
Declare @StateID int
Declare @SNumber Nvarchar(20) Select @SNumber = SNumber From Inserted
Select @StateID = SuperviseStateID From if @StateID=1
Begin
Set @Subject = '描述语句'+@SNumber
End
else if @StateID=2
Begin
Set @Subject = '描述语句'+@SNumber
End if @StateID>0
Begin
INSERT INTO B ([Subject], AfficheContent, AddUserID)
SELECT @Subject, @AfficheContent, @MsgToUserID
FROM Inserted
End
END
create table tb(c1 int,c2 int)
go
create trigger tri on ta for insert as
insert tb
select * from inserted
goinsert ta select 1,2 union select 3,4 union select 5,6select * from tb
/*
c1 c2
----------- -----------
5 6
3 4
1 2(3 row(s) affected)
*/
drop table ta,tb
ON A
FOR INSERT
AS
BEGIN
INSERT INTO B ([Subject], AfficheContent, AddUserID)
select case SuperviseStateID when 1 then '描述语句1'+SNumber when 2 then '描述语句2'+SNumber end,AfficheContent,MsgToUserID
from inserted
where SuperviseStateID>0
END
FROM Inserted 这一句,@Subject, @AfficheContent, @MsgToUserID 是3个定值,你把它们选了10遍,插入B表,
结果当然是B表中多了10条一模一样的记录。
ON A
FOR INSERT
AS
BEGIN
Declare @StateID int
Declare @SNumber Nvarchar(20) Select @SNumber = SNumber From Inserted --你这里实际上是选了Inserted集合中的第一条记录
Select @StateID = SuperviseStateID From Inserted --同上if @StateID=1
Begin
Set @Subject = '描述语句'+@SNumber
End
else if @StateID=2
Begin
Set @Subject = '描述语句'+@SNumber
End if @StateID>0
Begin
INSERT INTO B ([Subject], AfficheContent, AddUserID)
SELECT @Subject, @AfficheContent, @MsgToUserID --这3个参数经过上面的计算整合,变为3个值,这3个值都是由第一条记录产生的
FROM Inserted --这里的Inserted表显得没有任何意义,因为选出来的3个参数都不是从它里面出来的。它的唯一作用是把那3个定值重复了10遍。
End
END
------------------------------------
这种写法如果是批量更新的时候@SNumber永远是第一条数据,所以要用连接来做