Trigger Ranking pk e Hero Acumulativa
Segue abaixo a mesma:
Foi testada e esta 100% funcionando...
Tabelas a ser criadas:
Citação:
ALTER TABLE dbo.Character ADD PkCountWeb int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD PkCountWebHero int NOT NULL DEFAULT 0
Coluna PkCountWeb - responsavel pelo raking PK
Coluna PkCountWebHero - responsavel pelo ranking Hero
a coluna do Ranking PK, eu mantive a padrão da web do daldegam...
Trigger a ser criado depois das colunas criadas:
Citação:
CREATE TRIGGER [dbo].[RankingHeroPK]
ON [dbo].[Character]
after update
as
IF UPDATE(pkcount)
begin SET NOCOUNT ON;
Update [Character] set [Character].PkCountWebHero=PkCountWebHero+1, pkcount=0
Where [Character].accountid=(SELECT accountid FROM Inserted)
and [Character].name=(SELECT name FROM Inserted) and [character].pkcount < 0
end
IF UPDATE(pkcount)
begin SET NOCOUNT ON;
Update [Character] set [Character].PkCountWeb=PkCountWeb+1, pkcount=0
Where [Character].accountid=(SELECT accountid FROM Inserted)
and [Character].name=(SELECT name FROM Inserted) and [character].pkcount > 0
end
Pronto, fazendo assim ficará direto na Trigger nao precisando de rodar Job....
Dai pra frente é so montar o ranking com base nestas duas colunas criadas..
Ranking DIARIO / SEMANAL / MENSAL HERO e PK
Citação:
ALTER TABLE dbo.Character ADD PkCountWeb int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD PkCountWebHero int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD HeroDay int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD HeroWeek int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD HeroMonth int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD PKDay int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD PKWeek int NOT NULL DEFAULT 0
ALTER TABLE dbo.Character ADD PKMonth int NOT NULL DEFAULT 0
Rode a Trigger:
Citação:
CREATE TRIGGER [dbo].[RankingHeroPK]
ON [dbo].[Character]
after update
as
IF UPDATE(pkcount)
begin SET NOCOUNT ON;
Update [Character] set [Character].PkCountWebHero=PkCountWebHero+1,[Character].HeroDay=HeroDay+1,
[Character].HeroWeek=HeroWeek+1,[Character].HeroMonth=HeroMonth+1, pkcount=0
Where [Character].accountid=(SELECT accountid FROM Inserted)
and [Character].name=(SELECT name FROM Inserted) and [character].pkcount < 0
end
IF UPDATE(pkcount)
begin SET NOCOUNT ON;
Update [Character] set [Character].PkCountWeb=PkCountWeb+1,[Character].PKDay=PKDay+1,
[Character].PKWeek=PKWeek+1,[Character].PKMonth=PKMonth+1, pkcount=0
Where [Character].accountid=(SELECT accountid FROM Inserted)
and [Character].name=(SELECT name FROM Inserted) and [character].pkcount > 0
end
Zerando sem premiação
Criar Job rodar diariamente:
Citação:
UPDATE Character set PKDay = 0
UPDATE Character set HeroDay = 0
Criar Job para rodar semanalmente
Citação:
UPDATE Character set PKWeek = 0
UPDATE Character set HeroWeek = 0
Criar job para zerar mensalmente
Citação:
UPDATE Character set PKMonth = 0
UPDATE Character set HeroMonth = 0
Zerando com Premiação
Criar uma job para rodar diariamente
Citação:
declare @AccountIDPk varchar (10)
declare @AccountIDHero varchar(10)
Select top 1 @AccountIDPk=AccountID from character order by PKDay desc
Select top 1 @AccountIDHero=AccountID from character order by HeroDay desc
Update memb_info set gold=gold+10 where memb___id=@AccountIDPk
Update memb_info set gold=gold+10 where memb___id=@AccountIDHero
update Character set PKDay = 0
update Character set HeroDay = 0
Criar Job para rodar semanalmente:
Citação:
declare @AccountIDPk varchar (10)
declare @AccountIDHero varchar(10)
Select top 1 @AccountIDPk=AccountID from character order by PKWeek desc
Select top 1 @AccountIDHero=AccountID from character order by HeroWeek desc
Update memb_info set gold=gold+20 where memb___id=@AccountIDPk
Update memb_info set gold=gold+20 where memb___id=@AccountIDHero
update Character set PKWeek = 0
update Character set HeroWeek = 0
Criar Job para rodar Mensalmente:
Citação:
declare @AccountIDPk varchar (10)
declare @AccountIDHero varchar(10)
Select top 1 @AccountIDPk=AccountID from character order by PKMonth desc
Select top 1 @AccountIDHero=AccountID from character order by HeroMonth desc
Update memb_info set gold=gold+30 where memb___id=@AccountIDPk
Update memb_info set gold=gold+30 where memb___id=@AccountIDHero
update Character set PKMonth = 0
update Character set HeroMonth = 0
Caso a tabela de premiar não seja gold, é so mudar o nome nas linhas.... E os valores ficaram diario 10 golds / semanal 20 golds / mensal 30 golds
é so mudar tambem...
Caso você nao saiba criar JOB, existem tutoriais no forum ensinando a faze-los, é simples....
Créditos:
Project
Agradecimentos: Stefano.Aguiar
