Trigger Ranking pk e Hero Acumulativa

[jhonhy™]

Segue abaixo a mesma:

Foi testada e esta 100% funcionando...

Tabelas a ser criadas:

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:

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

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:

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:

UPDATE Character set PKDay = 0
UPDATE Character set HeroDay = 0

Criar Job para rodar semanalmente

UPDATE Character set PKWeek = 0
UPDATE Character set HeroWeek = 0

Criar job para zerar mensalmente

UPDATE Character set PKMonth = 0
UPDATE Character set HeroMonth = 0

Zerando com Premiação

Criar uma job para rodar diariamente

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:

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:

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

[GoianoOficial]

Por favor, poste 1 job pro ranking diario premiando os 3 primeiros

[fyre]

eu ja usei essa job, premia os 3 primeiros colocados.

declare @AccountID varchar (10)
declare @Name varchar(10)
declare @resetsWeek int
Select top 3 @AccountID=AccountID,@Name=Name,@resetsWeek=resetsWeek from character order by resetsWeek desc, name desc
Update MEMB_INFO set Gold=Gold+50 where memb___id=@AccountID

Update character set resetsWeek=0 <reseta o ranking

[GoianoOficial]

com essa query vc pode matar 100 vezes, so vai contabilizar +1 no ranking depois que reloga
seria possisvel colocar pra contabilizar pelo 3