8Xk4YXfV

1
2IF NOT EXISTS(
3    SELECT *
4    FROM sys.columns 
5    WHERE Name      = N'dmn_pk_count'
6      AND Object_ID = Object_ID(N'Character'))
7BEGIN
8   ALTER TABLE Character ADD dmn_pk_count INT NOT NULL DEFAULT 0
9END
10GO
11
12IF NOT EXISTS(
13    SELECT *
14    FROM sys.columns 
15    WHERE Name      = N'dmn_last_server_pk_count'
16      AND Object_ID = Object_ID(N'Character'))
17BEGIN
18   ALTER TABLE Character ADD dmn_last_server_pk_count INT NOT NULL DEFAULT 0
19END
20GO
21
22--UPDATE PK Ranking trigger
23
24IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[DmN_Update_Killer_Ranking]'))
25DROP TRIGGER [dbo].[DmN_Update_Killer_Ranking]
26GO
27
28CREATE TRIGGER [dbo].[DmN_Update_Killer_Ranking] ON [dbo].[Character]
29   AFTER UPDATE
30AS 
31BEGIN
32DECLARE @last_pk_count int
33DECLARE @Name varchar(50)
34DECLARE @PKCount int
35DECLARE @new_pk int
36SET NOCOUNT ON;
37    IF (UPDATE(PKCount))
38    BEGIN
39		SELECT @Name = Name, @PKCount = PKCount FROM inserted
40		SELECT @last_pk_count = dmn_last_server_pk_count FROM Character WHERE Name = @Name
41		
42		IF(@last_pk_count < @PKCount)
43		 BEGIN
44			SET @new_pk = @PKCount - @last_pk_count
45            UPDATE Character SET dmn_last_server_pk_count = @PKCount, dmn_pk_count = dmn_pk_count + @new_pk WHERE Name = @Name
46          END	
47    END
48END
49GO