Question

求写sqlserver 2008 insert触发器

有一个图书表，在插入新的信息时，如果该书不存在，则插入信息，如果存在，则增加总量，同时修改剩余量。
create table book (
book_id integer primary key identity(1,1),
type varchar(15),
book_name varchar(25),
book_author varchar(15),
publisher varchar(30),
price money,
sum Integer,
have Integer,
in_time Datetime,
flag integer,
remarks varchar(100),
foreign key(type) references booktype(type_name)
)

Answer 1

根据你的表结构，显然“该书不存在”的条件不能是id相同，我理解为type、book_name、book_author三者都相同，或者你还需要加上publisher也相同，那就自行添加条件吧，当然了，削减条件（例如book_name、book_author两个相同就行）也可。
要点：不能假设每次触发只影响一条记录，类似于insert book select * from book_backup的语句就会一次增加多条，所以，完备的方案应该使用游标遍历新增的各行。
如下：
create trigger TR_InsertBook
on book
for insert
as
begin
declare @id integer, @n integer
declare EnumInserted cursor for
select book_id from inserted
for read only
open EnumInserted
fetch EnumInserted into @id
while @@sqlstatus = 0
begin
select @n = count(*) from book t, inserted i
where i.book_id = @id
and t.book_id <> @id
and t.type = i.type
and t.book_name = i.book_name
and t.book_author = i.book_author
if @n = 1
begin
update book
set t.sum = t.sum + i.sum,
t.have = t.have + i.sum
from inserted i, book t
where i.book_id = @id
and t.book_id <> @id
and i.type = t.type
and i.book_name = t.book_name
and i.book_author = t.book_author
delete book where book_id = @id
end
fetch EnumInserted into @id
end
close EnumInserted
deallocate cursor EnumInserted
end

Answer 2

create trigger TG_BookInst
on book
after insert
as
begin
update book
set [sum] = book.[sum] + 1
from inserted as i
where book.book_id = i.book_id;
end
go
剩余是have吗?把字段加上去就可以了.

Answer 3

简单的做法的