[TUTORIAL]Log Deleted Characters

01/05/2011 23:38 Zektor#1
Ever wanted to ban a hacker that deletes his character, and changes his IP before you can ban him?

Well, you can log what character he deletes, and in what account and the date of the action.

Create a database called ban_info if you don't have one already and run this script in MSSQL:
Code:
USE [ban_info]
GO
/****** Object:  Table [dbo].[deletechar]    Script Date: 01/05/2011 19:03:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[deletechar](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[charname] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[accname] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[date] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_deletechar] PRIMARY KEY NONCLUSTERED 
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Now Go to your character table, and then go to Progammability section in the object dock (If you're using Microsoft SQL Server Management Studio 2005) And then Go to Stored Procedure,

Look for the "DBO.SP_CHAR_DELETE" stored procedure, right click it and click modify and copy and paste this script in the box:
[Only registered and activated users can see links. Click Here To Register...]
Code:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO



/******************************************************************
 * 작성자 : 한지욱
 * 작성일 : 2004.10.11(월)
 * 내  용 : 캐릭터정보삭제
 * 반환값 : -11 : user_character_secede Insert Fail
			-12 : user_suit_secede Insert Fail
			-13 : user_bag_secede Insert Fail
			-14 : user_storage_secede Insert Fail
			-15 : user_store_secede Insert Fail
			-16 : user_skill_secede Insert Fail
			-17 : user_slot_secede Insert Fail
			-18 : user_quest_doing_secede Insert Fail
			-19 : user_quest_done_secede Insert Fail
			-20 : 길드캐릭정보 삭제 실패
			-1 : user_character 삭제 실패
			0 : 성공
 * 수정자 : Hanjiwook
 * 수정일 :	2005.04.27 (WED)
 * 수정내역 : 캐릭터삭제시 캐릭터정보를 SECEDE Table로 복사한다.
 ******************************************************************
 * 수정자 : Hanjiwook
 * 수정일 :	2005.05.16 (MON)
 * 수정내역 : 캐릭터삭제시 개인상점정보를 SECEDE Table로 복사한다.
 ******************************************************************
 * 수정자 : Hanjiwook
 * 수정일 : 2005.08.04(THU)
 * 수정내역 : 캐릭터정보에 PK정보 추가 (wPKCount, wChaoticLevel, nShield)
 ******************************************************************
 * 수정자 : Hanjiwook
 * 수정일 : 2005.09.05(MON)
 * 수정내역 : 스탯클리어카운트 추가 (byStatClearCount)
 ******************************************************************
 * 수정자 : Hanjiwook
 * 수정일 : 2005.09.06(TUE)
 * 수정내역 : PC 기타정보 추가 (dwFlag)
 ******************************************************************
 * 수정자 : Choijihwan
 * 수정일 : 2006.02.06(MON)
 * 수정내역 : user_character테이블 PVP Columns 추가.
				DWORD	dwPVPPoint;		// PVP 점수
				WORD	wWinRecord;		// 승리 횟수
				WORD	wLoseRecord;	// 패배 횟수
				WORD	wDrawRecord;	// 비긴 횟수
 ****************************************************************** 
 * 수정자 : Choijihwan
 * 수정일 : 2006.12.26(WEB)
 * 수정내역 :	Add "dwSupplyPoint" column
				DWORD	dwSupplyPoint;		
 ******************************************************************/
ALTER PROCEDURE [dbo].[SP_CHAR_DELETE]
	@character_no			varchar(18)			,
	@sp_rtn					INT			OUTPUT
AS
	DECLARE @rowcnt				int
	DECLARE @v_character_name	varchar(40)
	DECLARE @v_guild_code		varchar(10)
	DECLARE @v_user_name		varchar(40)
BEGIN
	

	SET @v_character_name 	= dbo.FN_GetCharName(@character_no)
	
	SET @v_guild_code		= dbo.FN_GetGuildCode(@v_character_name)
	SET @v_user_name		= dbo.FN_GetUserID(dbo.FN_GetUserNo(@character_no))
	INSERT INTO ban_info.dbo.deletechar(charname,accname,date) VALUES (@v_character_name, @v_user_name, CAST(GETDATE() as varchar));
	
	IF LEN(@v_guild_code) > 0
		BEGIN
			SET @sp_rtn = -20
			RETURN
		END

	BEGIN TRANSACTION		

	/*	user_character_secede	*/
	INSERT INTO user_character_secede 
		(
			character_no		,	 
			character_name		, 
			user_no				, 
			dwAdv				,
			dwPeerage			,
			dwExp				, 
			dwMoney				, 
			dwStoreMoney		, 
			dwStorageMoney		,
			nHP					, 
			nMP					, 
			wStr				, 
			wDex				, 
			wCon				, 
			wSpr				, 
			wPosX				, 
			wPosY				, 
			wRetPosX			, 
			wRetPosY			, 
			wMapIndex			, 
			wRetMapIndex		, 
			wStatPoint			, 
			wSkillPoint			, 
			wLevel				, 
			byPCClass			, 
			byDirection			, 
			byRetDirection		,
			bySkillClearCount	,
			byStatClearCount	,
			wPKCount			,
			wChaoticLevel		,
			nShield				,
			dwFlag				,
			login_flag			, 
			ipt_date			, 
			ipt_time			, 
			upt_time			, 
			login_time			, 
			logout_time			, 
			user_ip_addr		, 
			del_date			, 
			del_time			,
			dwPVPPoint			,		
			wWinRecord			,
			wLoseRecord			,
			wDrawRecord			,
			dwSupplyPoint
	)
	SELECT	character_no		, 
			character_name		, 
			user_no				, 
			dwAdv				,
			dwPeerage			,
			dwExp				, 
			dwMoney				, 
			dwStoreMoney		, 
			dwStorageMoney		, 
			nHP					, 
			nMP					, 
			wStr				, 
			wDex				, 
			wCon				, 
			wSpr				, 
			wPosX				, 
			wPosY				, 
			wRetPosX			, 
			wRetPosY			, 
			wMapIndex			, 
			wRetMapIndex		, 
			wStatPoint			, 
			wSkillPoint			, 
			wLevel				, 
			byPCClass			, 
			byDirection			, 
			byRetDirection		,
			bySkillClearCount	,
			byStatClearCount	,
			wPKCount			,
			wChaoticLevel		,
			nShield				,
			dwFlag				,
			login_flag			, 
			ipt_date			, 
			ipt_time			, 
			upt_time			, 
			login_time			, 
			logout_time			, 
			user_ip_addr		, 
			convert(char(8),getdate(),112), 
			GetDate()			,
			dwPVPPoint			,		
			wWinRecord			,
			wLoseRecord			,
			wDrawRecord			,
			dwSupplyPoint		
	FROM	user_character
	WHERE	character_no = @character_no

	IF (@@error <> 0 or @rowcnt<>1) 
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -11
			RETURN
		END	

	/*	user_suit_secede	*/
	INSERT INTO user_suit_secede 
		(	
			character_no	, 
			line_no			, 
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate
		)
	SELECT	character_no	, 
			line_no			, 
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate
	FROM	user_suit
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -12
			RETURN
		END		

	/*	user_bag_secede	*/
	INSERT INTO user_bag_secede 
		(	
			character_no	, 
			line_no			, 
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate
		)
	SELECT	character_no	, 
			line_no			, 
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate
	FROM	user_bag
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -13
			RETURN
		END		

	/*	user_storage_secede	*/
	INSERT INTO user_storage_secede 
		(	
			character_no	, 
			line_no			, 
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate	
		)
	SELECT	character_no	, 
			line_no			, 
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate
	FROM	user_storage
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -14
			RETURN
		END

	/*	user_store_secede	*/
	INSERT INTO user_store_secede 
		(	
			character_no	, 
			line_no			,
			dwPrice			,
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate	
		)
	SELECT	character_no	, 
			line_no			,
			dwPrice			,
			byHeader		, 
			wIndex			, 
			dwSerialNumber	, 
			info			, 
			upt_time		,
			reg_bindate		,
			exp_bindate
	FROM	user_store
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -15
			RETURN
		END

	/*	user_skill_secede	*/
	INSERT INTO user_skill_secede (	character_no, 
									line_no		, 
									info		, 
									ipt_time	, 
									upt_time	)
	SELECT	character_no	, 
			line_no			, 
			info			, 
			ipt_time		, 
			upt_time
	FROM	user_skill
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -16
			RETURN
		END

	/*	user_slot_secede	*/
	INSERT INTO user_slot_secede (	character_no, 
									line_no		, 
									info		, 
									ipt_time	, 
									upt_time	)
	SELECT	character_no, 
			line_no		, 
			info		, 
			ipt_time	, 
			upt_time
	FROM	user_slot
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -17
			RETURN
		END	

	/*	user_quest_doing_secede	*/
	INSERT INTO user_quest_doing_secede (	character_no, 
											q_index		, 
											q_count_0	,
											q_count_1	,
											q_count_2	,
											q_count_3	,
											q_start_time,
											upt_time	)
	SELECT	character_no, 
			q_index		, 
			q_count_0	,
			q_count_1	,
			q_count_2	,
			q_count_3	,
			q_start_time,
			upt_time
	FROM	user_quest_doing
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -18
			RETURN
		END	

	/*	user_quest_done_secede	*/
	INSERT INTO user_quest_done_secede (	character_no, 
											q_index		, 
											upt_time	)
	SELECT	character_no, 
			q_index		, 
			upt_time
	FROM	user_quest_done
	WHERE	character_no = @character_no

	IF @@error <> 0
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -19
			RETURN
		END		
	-- user_character 삭제
	DELETE FROM  user_character
	WHERE character_no = @character_no
	
	SELECT @rowcnt = @@rowcount
	
	IF (@@error <> 0 or @rowcnt<>1) 
		BEGIN
			ROLLBACK TRANSACTION
			SET @sp_rtn = -1
			RETURN
		END
	
	COMMIT TRANSACTION
	SET @sp_rtn = 0
	RETURN 0
	
END
And run EXECUTE. You should get no errors, and now when characters delete their character it will log in the ban_info.dbo.deletechar table.

As you can see I've added
Code:
DECLARE @v_user_name		varchar(40)

SET @v_user_name		= dbo.FN_GetUserID(dbo.FN_GetUserNo(@character_no))
INSERT INTO ban_info.dbo.deletechar(charname,accname,date) VALUES (@v_character_name, @v_user_name, CAST(GETDATE() as varchar));

The DECLARE, basically declares that variable so we can use it later in the script, it initializes it with the data type specified. The SET @v_user_name basically sets a new value based on the returned function Dekaron uses, since we already have functions to retrieve information we need it makes our job a whole lot easier to obtain the account name. The INSERT INTO inserts the information into the table.

Hope you guys understand what I've done here

Enjoy!
-Created by Me
01/05/2011 23:56 daddycool.#2
Nice, testing :D
01/06/2011 00:36 Zektor#3
I made this because Silkbotter was deleting his character every time we attempted to ban his character, so I made this :)

Edit:
Altered the create script for the deletechar table to add incremental ID.
01/06/2011 08:34 pieter#4
or just search character..user_character_secede for the charname ;)
01/06/2011 16:12 Zektor#5
This makes it simple and easy to find
Edit:
Also you get exact time of deletion, and you quickly know the user ID searching for the user_no in the account db
01/06/2011 23:19 Decima#6
i hate to rain on ur parade buddy, but check out the 'user_character_secede' table in the characters DB, it logs all deleted characters, IP it was deleted from, exact time it was deleted, user_no, everything, all u have to do is one simple query.

select * from user_character_secede where user_no = "USERNUMBER"

your way is much more complicated then mine :)
01/06/2011 23:42 caper#7
Quote:
Originally Posted by Decima View Post
your way is much more complicated then mine :)
I've been saying this for a while now. Don't get me wrong, I write code also and can appreciate all the work people do on here but half the things I see posted are just reinventing the wheel.

If you write your code to make use of already established "stored procedures" and database tables, you would only have to do a minimal amount of work.

The game already knows how to do things from generating unique numbers to mailing items to logging events.

If nothing else I guess it's good practice for people who want to learn how to interface with databases.
01/07/2011 00:10 Zektor#8
Quote:
Originally Posted by caper View Post
I've been saying this for a while now. Don't get me wrong, I write code also and can appreciate all the work people do on here but half the things I see posted are just reinventing the wheel.

If you write your code to make use of already established "stored procedures" and database tables, you would only have to do a minimal amount of work.

The game already knows how to do things from generating unique numbers to mailing items to logging events.

If nothing else I guess it's good practice for people who want to learn how to interface with databases.
Oh nice, I had no idea. I thought it was just a copy of the original user_character except it kept deleted characters.