help me find please

10/15/2012 20:12 Svinseladden#1
heya.. i found a item mall point for each kill here 1 time but i can't find it again. can someone please help me find it thanks..
10/15/2012 20:24 taZツ#2
Quote:
Originally Posted by player1up View Post
This trigger updates points based on kills ( K1 )

It fires when K1 is updated in the chars table. I do 5 pts for each kill.

Code:
USE [PS_GameData]
GO

/****** Object:  Trigger [dbo].[PointsForKills]    Script Date: 09/27/2012 11:57:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

                                                     
                                             

create trigger [dbo].[PointsForKills]
on [PS_GameData].[dbo].[Chars]
after update
as
IF (UPDATE (k1))
begin
declare @oldk1 int
declare @newk1 int
set @oldk1 = (select k1 from deleted)
set @newk1 = (select k1 from inserted)
if @oldk1<@newk1
	begin
	update PS_userdata.dbo.users_master
	set point=point+5 
	from PS_userdata.dbo.users_master m,inserted i where m.userUID=i.useruid
	end
/*if I wanted to do 500 points per x000 kills I would do something like this

if (convert ( varchar (5),@newk1) like '%000')
	begin
	update PS_userdata.dbo.users_master
	set point=point+500 
	from PS_userdata.dbo.users_master m,inserted i where m.userUID=i.useruid
        end
This would give 500 points to every k1 value that is "like" 1000 or 2000 or 3000 etc, not just 1000 kills
*/
	end
end

GO
Pseudo code of what the trigger is doing:
1. Trigger waits for K1 to be updated
2. If k1 gets updated, go compare the old value ( deleted ) with the new value ( inserted ) that was just added.
3. If old value is less than new value, go update users_master points, where userid of chars table matches userid of Users_master ( this way you don't update everyone's points lol )

Appendix:
A. There is a "from" statement because we are comparing 2 tables and you have to reference both inside. There are many ways of doing this ( joins, full object names etc ) but this is how i did it.
B. /* this begins a comment block in SQL
C. */ this ends a comment block in SQL
D. Understand what you are doing before doing it :)
E. Added points take a little bit to show on the client side, but a relog is NOT necessary.
From this thread:
[Only registered and activated users can see links. Click Here To Register...]
10/15/2012 20:38 Svinseladden#3
ty ty ty taz saved my life:-)
10/15/2012 22:34 JohnHeatz#4
#Moved to the right section
10/16/2012 15:37 Svinseladden#5
Quote:
Originally Posted by JohnHeatz View Post
#Moved to the right section
thanks and sorry..

btw this isnt working good:-(

some get points and other don't and i hade a problem with it. gave me and error in the last end there. i removed it but not working well now:-(


Msg 102, Level 15, State 1, Procedure PointsForKills, Line 32
Incorrect syntax near 'end'.
10/16/2012 17:46 cypherxxl#6
Hm does this work or not?
10/16/2012 20:44 Svinseladden#7
it's a bugg in it i'm trying to find out what it is but well not found what it is yet..

mabe ppl think this is stupid but this works

or the stuff i have tried it hehehe

i added this too usp_Insert_Action_Log_E



Quote:
IF (@ActionType = 103)
begin
Update PS_Userdata.dbo.Users_Master SET Point = Point + 5 WHERE UserID = @UserID
END
this gives 5 points for each kill and yes i think you need to relogg to get your points

might be a bugg in this but for now i havent found it..

how do you find usp_Insert_Action_Log_E

open the database PS_GameLog. go to Stored Prosedures and open that

there you find usp_Insert_Action_Log_E right click on it and click modify

this will open it and before the :

Quote:
SET @Sql = N'
INSERT INTO PS_GameLog.dbo.ActionLog
(UserID, UserUID, CharID, CharName, CharLevel, CharExp, MapID, PosX, PosY, PosZ, ActionTime, ActionType,
Value1, Value2, Value3, Value4, Value5, Value6, Value7, Value8, Value9, Value10, Text1, Text2, Text3, Text4)
VALUES(@UserID, @UserUID, @CharID, @CharName, @CharLevel, @CharExp, @MapID, @PosX, @PosY, @PosZ, @ActionTime, @ActionType,
@Value1, @Value2, @Value3, @Value4, @Value5, @Value6, @Value7, @Value8, @Value9, @Value10, @Text1, @Text2, @Text3, @Text4)'
just put in

Quote:
IF (@ActionType = 103)
begin
Update PS_Userdata.dbo.Users_Master SET Point = Point + 5 WHERE UserID = @UserID
END
execute and it will give you 5 points for each kill

if this is wrong please tell me hahah i get the kills so should be ok. and as i know action 103 is kill and this is should work