求一个UPDATE触发器

[zhaokun521]

需求是这样的:
统计表结构是这样的ID自增长,LDAY计天数int,LHOUR计小时int,SDAY计天数int,SHOUR计小时int
UPDATE这张表时,判断LHOUR如果到24时,LDAY加1,相同的SHOUR到24时,SDAY加1

[zhaokun521]

求个详细代码好吗，谢谢

[arrow_gx]

ID 自增长
LDAY 计天数int
LHOUR 计小时int
SDAY计 天数int
SHOUR 计小时int

ALTER TRIGGER [dbo].[test_001]
ON [dbo].[test]
for DELETE,insert
AS
declare @tmp1 int

BEGIN
IF not Update(LHOUR)
begin
select @tmp1=LHOUR from inserted
if @tmp1=24
update test set LDAY=LDAY+1
end

IF not Update(SHOUR)
begin
select @tmp1=SHOUR from inserted
if @tmp1=24
update test set SDAY=SDAY+1
end



END

[arrow_gx]

[Quote=引用楼主 zhaokun521 的帖子:]
需求是这样的:
统计表结构是这样的ID自增长,LDAY计天数int,LHOUR计小时int,SDAY计天数int,SHOUR计小时int
UPDATE这张表时,判断LHOUR如果到24时,LDAY加1,相同的SHOUR到24时,SDAY加1
[/Quote]
create table test(
ID int IDENTITY(1,1) NOT NULL,
LDAY int,
LHOUR int ,
SDAY计 int ,
SHOUR int
)


CREATE TRIGGER [dbo].[test_001]
ON [dbo].[test]
for UPDATE
AS
declare @tmp1 int
BEGIN
IF not Update(LHOUR)
begin
select @tmp1=LHOUR from inserted
if @tmp1=24
update test set LDAY=LDAY+1
end

IF not Update(SHOUR)
begin
select @tmp1=SHOUR from inserted
if @tmp1=24
update test set SDAY=SDAY+1
end

END

[arrow_gx]

create table test( 
ID int IDENTITY(1,1) NOT NULL, 
LDAY int, 
LHOUR int , 
SDAY计 int , 
SHOUR int 
) 


CREATE TRIGGER [dbo].[test_001] 
  ON  [dbo].[test] 
  for UPDATE 
AS 
declare @tmp1 int 
BEGIN 
IF not Update(LHOUR)  
    begin 
        select @tmp1=LHOUR from inserted 
        if @tmp1=24 
            update test set LDAY=LDAY+1 
    end 

IF not Update(SHOUR)  
    begin 
        select @tmp1=SHOUR from inserted 
        if @tmp1=24 
            update test set SDAY=SDAY+1 
    end 

END

[hanjs]

感觉应该是 24的倍数时 day 加1

[hanjs]

那么是否 小时数变为0了？

[hanjs]

不知道你们写的触发器为何加 not update？

然后还是指定触发器为 for update，这样就更新 hour字段 永远也不触发啊！

[arrow_gx]

呵呵，例程而已，没注意条件，改一下

create table test001( 
ID int IDENTITY(1,1) NOT NULL, 
LDAY int, 
LHOUR int , 
SDAY int , 
SHOUR int 
) 

--insert test001(lday,lhour,sday,shour) values (1,2,3,4)

--update test001 set lhour=24
--select * from test001
--drop table test001

Create TRIGGER [dbo].[trg_test_001] 
  ON  [dbo].[test001] 
  for UPDATE 
AS 
declare @tmp1 int 
BEGIN 
IF Update(LHOUR)  
    begin 
        select @tmp1=LHOUR from inserted 
        if @tmp1=24 
            update test001 set LDAY=LDAY+1 
    end 

IF Update(SHOUR)  
    begin 
        select @tmp1=SHOUR from inserted 
        if @tmp1=24 
            update test001 set SDAY=SDAY+1 
    end 

END

[zhaokun521]

谢谢大哥哥,好像有用了,哈

[昵称被占用了]

arrow_gx的触发器不能处理一次修改多条记录的情况，==我帮你写个

ps：好像表有多条记录就有问题

[昵称被占用了]

已经结贴.......希望搂主能看到

Create TRIGGER [dbo].[trg_test_001] 
  ON  [dbo].[test001] 
  for UPDATE 
AS 
BEGIN 
IF Update(LHOUR)  
    begin 
            update a set 
                   LDAY=case when i.LHOUR>=24 then isnull(a.LDAY,0)+1 else isnull(a.LDAY,0) end,
                   LHOUR=case when i.LHOUR>=24 then isnull(a.LHOUR,0)-24 else isnull(a.LHOUR,0) end
            from test001 a,
                 inserted i
            where a.id=i.id
           
    end 

IF Update(SHOUR)  
    begin 
            update a set 
                   SDAY=case when i.SHOUR>=24 then isnull(a.SDAY,0)+1 else isnull(a.SDAY,0) end,
                   SHOUR=case when i.SHOUR>=24 then isnull(a.SHOUR,0)-24 else isnull(a.SHOUR,0) end
            from test001 a,
                 inserted i
            where a.id=i.id
    end 

END