一个触发器的写法？

[喜欢黄家驹]

第一张表: A 是基本表用来记录 用户的注册时间和电话号码 如下:
registertime telnumber
2008-1-1 00:33:44 0991232112
2008-1-1 01:12:33 0993443313
2008-1-2 01:12:33 0993443321
2008-1-3 22:33:44 0999.......
... ...
... ...

第二张表:B 用来统计上面的数据 结构如下: regtime 为短时间格式
字段：“0991”、”0993“、”0999“表示该区号下的用户数；
字段：“其他” 表示不在此区号下的人数
regtime 0991 0993 0999 其他
2008-1-1 1(个) 1 0 0
2008-1-2 0 1 0 0
2008-1-3 0 0 1 0
... ... ... ... ...

请问如何编写触发器, 当第一张A有新用户注册时 更新第二张表B?

必须是触发器!

先谢谢各位了!

[liangCK]

create trigger tri on 表A
for insert
as
   if not exists(select 1 from 表B where regtime=convert(char(10),getdate()))
   begin
      insert into 表B values(convert(char(10),getdate(),0,0,0,0)
   end
   --update这里写你的update语句. 
go

[喜欢黄家驹]

写完整点 好吗？
我不太懂！

[liangCK]

create trigger tri on 表A
for insert
as
   if not exists(select 1 from 表B where regtime=convert(char(10),getdate()))
   begin
      insert into 表B values(convert(char(10),getdate(),0,0,0,0)
   end
   declare @区号 char(4)
   select @区号=区号 from inserted
   if @区号='0991' 
      update 表B set [0991]=[0991]+1 where regtime=convert(char(10),getdate())
   else if @区号='0993'
      update 表B set [0993]=[0993]+1 where regtime=convert(char(10),getdate())
   else if @区号='0999'
      update 表B set [0999]=[0999]+1 where regtime=convert(char(10),getdate())
   else 
      update 表B set [其他]=[其他]+1 where regtime=convert(char(10),getdate())
go

[昵称被占用了]

create trigger tri on 表A
for insert
as

update b set
   [0991]=isnull(b.[0991],0)+isnull(t.[0991],0),
   [0993]=isnull(b.[0993],0)+isnull(t.[0993],0),
   [0999]=isnull(b.[0999],0)+isnull(t.[0999],0),
   [其他]=isnull(b.[其他],0)+isnull(t.[其他],0)
from 表B b,(
select convert(varchar(10),registertime,120) as regtime,
  sum(case left(telnumber,4) when '0991' then 1 else 0 end) as [0991],
  sum(case left(telnumber,4) when '0993' then 1 else 0 end) as [0993],
  sum(case left(telnumber,4) when '0999' then 1 else 0 end) as [0999],
  sum(case when left(telnumber,4) not in ('0991','0993','0999') then 1 else 0 end) as [其他]
from inserted
group by convert(varchar(10),registertime,120)
) as t
where b.regtime=t.regtime

insert 表B(regtime,[0991],[0993],[0999],[其他])
select convert(varchar(10),t.registertime,120) as regtime,
  sum(case left(telnumber,4) when '0991' then 1 else 0 end) as [0991],
  sum(case left(telnumber,4) when '0993' then 1 else 0 end) as [0993],
  sum(case left(telnumber,4) when '0999' then 1 else 0 end) as [0999],
  sum(case when left(telnumber,4) not in ('0991','0993','0999') then 1 else 0 end) as [其他]
from inserted t
where not exists (
   select 1 from 表B 
   where regtime=convert(varchar(10),t.registertime,120)
   )
group by convert(varchar(10),t.registertime,120)

go

[喜欢黄家驹]

谢谢各位！
我试试 如果成功了就结贴了！

[lucky749]

create table Lettest
(
id int identity(1,1),
RegeterTime datetime,
teleNumber varchar(30)
)

create table Lettests
(
id int identity(1,1),
RegeterTime datetime,
One int,
two int,
three int,
others int
)

create trigger backupdata on Lettest
for Insert
as
if not exists(select * from Lettests where RegeterTime=convert(varchar(10),getdate(),120))
begin
insert into lettests values(convert(varchar(10),getdate(),120),0,0,0,0)
end
declare @teleteNumber varchar(20)
select @teleteNumber=substring(teleNumber,1,4) from inserted
if @teleteNumber='0991'
update Lettests set one=one+1 where RegeterTime=convert(varchar(10),getdate(),120)
else if @teleteNumber='0992'
update Lettests set two=two+1 where RegeterTime=convert(varchar(10),getdate(),120)
else if @teleteNumber='0993'
update Lettests set three=three+1 where RegeterTime=convert(varchar(10),getdate(),120)
else
update Lettests set others=others+1 where RegeterTime=convert(varchar(10),getdate(),120)