CREATE TRIGGER trig1
on T1
FOR INSERT
AS
BEGIN
IF NOT EXISTS(SELECT 1 FROM T2 a,inserted b WHERE a.CLASS = b.CLASS)
INSERT INTO T2(CLASS,NUMBER) SELECT CLASS,1 FROM INSERTED
ELSE
UPDATE T2 SET NUMBER=NUMBER+1 FROM T2 a,INSERTED b WHERE a.CLASS = b.CLASS
END
on T1
FOR INSERT
AS
BEGIN
IF NOT EXISTS(SELECT 1 FROM T2 a,inserted b WHERE a.CLASS = b.CLASS)
INSERT INTO T2(CLASS,NUMBER) SELECT CLASS,1 FROM INSERTED
ELSE
UPDATE T2 SET NUMBER=NUMBER+1 FROM T2 a,INSERTED b WHERE a.CLASS = b.CLASS
END
insert t1 select ...
union all
select ...
所以:
-------------------------------
create trigger t_i_t1 on t1
for insert
as
begin
declare @a table (class varchar(10),num int)
insert @a select class,sum(1) from inserted update t2 set number=t2.number+tem.num from @a tem where tem.class=t2.class
insert t2 (class,number) select * from @a where class not in (select class from t2)
end
on t1
for insert
as
if exists(select 1 from t2 a inner join inserted b on a.class=b.class)
update t2 set number=number+1 from t2 a inner join inserted b on a.class=b.class
else
insert into t2 select class,'1' from inserted
go
on t1
for insert
as
if exists (select * from t2 where class =(select class from inserted))
begin
update t2 set number=number+1 where class=(select class from inserted)
end
else
begin
insert into t2 select (select class from inserted),1
end