USE [v3_character]
GO
/****** 对象: Trigger [dbo].[zscfq] 脚本日期: 07/21/2010 08:06:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[zscfq]
ON [dbo].[CharacterRebirth]
FOR INSERT,UPDATE
AS
DECLARE @cs int,@na varchar(20)
SELECT @cs=inserted.rebirthCount from inserted
SELECT @na=inserted.characterName from inserted
IF (@cs > 0 and @cs < 10)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-51,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 10 and @cs < 20)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-49,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 20 and @cs < 30)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-48,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 30 and @cs < 40)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-47,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 40 and @cs < 50)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-46,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 50 and @cs < 100)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("m",-45,getdate()) WHERE (characterName = @na)
END上面是我写的针对这个表 CharacterRebirth 如果插入或者修改数据, 根据rebirthCount的值
减CharacterRebirth表lastRebirthDate字段的时间,用了一会 发现一会行 一会不行
求助
GO
/****** 对象: Trigger [dbo].[zscfq] 脚本日期: 07/21/2010 08:06:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[zscfq]
ON [dbo].[CharacterRebirth]
FOR INSERT,UPDATE
AS
DECLARE @cs int,@na varchar(20)
SELECT @cs=inserted.rebirthCount from inserted
SELECT @na=inserted.characterName from inserted
IF (@cs > 0 and @cs < 10)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-51,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 10 and @cs < 20)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-49,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 20 and @cs < 30)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-48,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 30 and @cs < 40)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-47,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 40 and @cs < 50)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("d",-46,getdate()) WHERE (characterName = @na)
END
IF (@cs >= 50 and @cs < 100)
BEGIN
UPDATE CharacterRebirth SET lastRebirthDate=DateAdd("m",-45,getdate()) WHERE (characterName = @na)
END上面是我写的针对这个表 CharacterRebirth 如果插入或者修改数据, 根据rebirthCount的值
减CharacterRebirth表lastRebirthDate字段的时间,用了一会 发现一会行 一会不行
求助
SELECT @na=inserted.characterName from inserted
這個對於批量操作
就會出現問題
FOR INSERT, UPDATE
AS
UPDATE CharacterRebirth
SET lastRebirthDate = DATEADD(
"d", CASE
WHEN i.rebirthCount BETWEEN 0 AND 10 THEN -51
WHEN i.rebirthCount<20 THEN -49
WHEN i.rebirthCount<30 THEN -48
WHEN i.rebirthCount<40 THEN -47
WHEN i.rebirthCount<50 THEN -46
ELSE -45
END, GETDATE()
)
FROM INSERTED i
WHERE CharacterRebirth.characterName = i.characterName
GO
FOR INSERT, UPDATE
AS
UPDATE CharacterRebirth
SET lastRebirthDate = DATEADD(
"d", CASE
WHEN i.rebirthCount<10 THEN -51
WHEN i.rebirthCount<20 THEN -49
WHEN i.rebirthCount<30 THEN -48
WHEN i.rebirthCount<40 THEN -47
WHEN i.rebirthCount<50 THEN -46
ELSE -45
END, GETDATE()
)
FROM INSERTED i
WHERE CharacterRebirth.characterName = i.characterName
AND i.rebirthCount>=0
GO