hello guys ( some Triggers ) about point unique

where_love2003 · 04/21/2015, 20:56

Excuse me
first so sorry about my bad English Language
Second
i have this tab
_UniqueKill

and i create _Ranking

i want to if any one kill unique get point=total
like If Charname Kille TigerGirl=20 total
i do some thing like this Triggers
but i do not how it work

Quote:

CREATE TRIGGER trgAddTotal
ON Dan_com.._UniqueKill FOR INSERT
AS
DECLARE @charname int;
SET @charname = (SELECT TOP (1) charname FROM Dan_com.._UniqueKill ORDER BY ID DESC);
if not exists (select charname from Dan_com.._Ranking where charname = @charname)
DECLARE @MobName int;
SET @MobName = (SELECT unique_name FROM Dan_com.._UniqueKill WHERE unique_name = @MobName);
if(@MobName='MOB_CH_TIGERWOMAN')
update _Ranking set Total=Total+20 where charname = @charname
if(@MobName='MOB_EU_KERBEROS')
update _Ranking set Total=Total+20 where charname = @charname
if(@MobName='MOB_AM_IVY')
update _Ranking set Total=Total+30 where charname = @charname
if(@MobName='MOB_OA_URUCHI')
update _Ranking set Total=Total+40 where charname = @charname
if(@MobName='MOB_KK_ISYUTARU')
update _Ranking set Total=Total+60 where charname = @charname
if(@MobName='MOB_TK_BONELORD')
update _Ranking set Total=Total+80 where charname = @charname
if(@MobName='MOB_RM_TAHOMET')
update _Ranking set Total=Total+90 where charname = @charname
if(@MobName='MOB_JUPITER_JUPITER')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_BAAL')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_BABILION')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_YUNO')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_THE_EARTH1' or @MobName='MOB_JUPITER_THE_EARTH2')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_DARK_DOG' or @MobName='MOB_JUPITER_DARK_DOG2')
update _Ranking set Total=Total+120 where charname = @charname

if(@MobName='MOB_TQ_WHITESNAKE')
update _Ranking set Total=Total+1000 where charname = @charname
if(@MobName='MOB_ARABIA_KIDEMONAS'
OR @MobName='MOB_MADELYN_1'
OR @MobName='MOB_MADELYN_2'
OR @MobName='MOB_MADELYN_3'
OR @MobName='MOB_MADELYN_4'
OR @MobName='MOB_MADELYN_5'
OR @MobName='MOB_MADELYN_6')
update _Ranking set Total=Total+200 where charname = @charname

if(@MobName='MOB_GM_DEVIN' OR @MobName='MOB_GM_LOOK')
update _Ranking set Total=Total+150 where charname = @charname

i hope any one can help me

~~NotEvenMad~~ · 04/21/2015, 20:58

First thing, why the hell do you declare "charname" as INT?

INT is numeric (1,2,3,4,5,6,7,8,9)

You need to declare it as varchar.

Same with Mobname.

where_love2003 · 04/21/2015, 21:00

Quote:

Originally Posted by NotEvenMad

First thing, why the hell do you declare "charname" as INT?

INT is numeric (1,2,3,4,5,6,7,8,9)

You need to declare it as varchar.

Same with Mobname.

i`am not a professional on sql

~~NotEvenMad~~ · 04/21/2015, 21:10

Quote:

Originally Posted by where_love2003

i`am not a professional on sql

Your Trigger makes no sense at all, if you look at this part.

Code:

if not exists (select charname from Dan_com.._Ranking where charname = @charname)

If it doesn't exist what should it do? I mean if it don't exist shouldn't it INSERT ? cause if it doesn't exist then it cannot update something? Make sense huh?

Also I do not see that you have something in your DB named "total" at all, you need to add an column in _Ranking which has total and should be "BigINT" or "INT" depends on how many points that should be in there.

My tip;
Go learn difference betweeen INT, Varchar and so on.

Change DECLARE @MobName int;
TO DECLARE @MobName varchar(MAX);

Change DECLARE @charname int;
TO DECLARE @charname varchar(MAX);

where_love2003 · 04/21/2015, 21:15

Quote:
Originally Posted by NotEvenMad
Your Trigger makes no sense at all, if you look at this part.
Code:
if not exists (select charname from Dan_com.._Ranking where charname = @charname)
If it doesn't exist what should it do? I mean if it don't exist shouldn't it INSERT ? cause if it doesn't exist then it cannot update something? Make sense huh?

Also I do not see that you have something in your DB named "total" at all, you need to add an column in _Ranking which has total and should be "BigINT" or "INT" depends on how many points that should be in there.

My tip;
Go learn difference betweeen INT, Varchar and so on.

Change DECLARE @MobName int;
TO DECLARE @MobName varchar(MAX);

Change DECLARE @charname int;
TO DECLARE @charname varchar(MAX);

first i told you i`am a not an professional to sql
i just want help

Any Help

Quote:
Originally Posted by NotEvenMad
Your Trigger makes no sense at all, if you look at this part.
Code:
if not exists (select charname from Dan_com.._Ranking where charname = @charname)
If it doesn't exist what should it do? I mean if it don't exist shouldn't it INSERT ? cause if it doesn't exist then it cannot update something? Make sense huh?

Also I do not see that you have something in your DB named "total" at all, you need to add an column in _Ranking which has total and should be "BigINT" or "INT" depends on how many points that should be in there.

My tip;
Go learn difference betweeen INT, Varchar and so on.

Change DECLARE @MobName int;
TO DECLARE @MobName varchar(MAX);

Change DECLARE @charname int;
TO DECLARE @charname varchar(MAX);

you mean that will be work ?

Code:

CREATE TRIGGER trgAddTotal
ON Dan_com..__srSquirrelMod_UniqueKillLog FOR INSERT 
AS
    DECLARE @charname varchar(MAX);
    SET @charname = (SELECT TOP (1) charname FROM Dan_com..__srSquirrelMod_UniqueKillLog ORDER BY ID DESC);
	if not exists (select charname from Dan_com.._Ranking where charname = @charname)
    DECLARE @MobName varchar(MAX);
    SET @MobName = (SELECT unique_name FROM Dan_com..__srSquirrelMod_UniqueKillLog WHERE unique_name = @MobName);
   if(@MobName='MOB_CH_TIGERWOMAN')
update _Ranking set Total=Total+20 where charname = @charname
if(@MobName='MOB_EU_KERBEROS')
update _Ranking set Total=Total+20 where charname = @charname
if(@MobName='MOB_AM_IVY')
update _Ranking set Total=Total+30 where charname = @charname
if(@MobName='MOB_OA_URUCHI')
update _Ranking set Total=Total+40 where charname = @charname
if(@MobName='MOB_KK_ISYUTARU')
update _Ranking set Total=Total+60 where charname = @charname
if(@MobName='MOB_TK_BONELORD')
update _Ranking set Total=Total+80 where charname = @charname
if(@MobName='MOB_RM_TAHOMET')
update _Ranking set Total=Total+90 where charname = @charname
if(@MobName='MOB_JUPITER_JUPITER')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_BAAL')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_BABILION')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_YUNO')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_THE_EARTH1' or @MobName='MOB_JUPITER_THE_EARTH2')
update _Ranking set Total=Total+120 where charname = @charname
if(@MobName='MOB_JUPITER_DARK_DOG' or @MobName='MOB_JUPITER_DARK_DOG2')
update _Ranking set Total=Total+120 where charname = @charname

if(@MobName='MOB_TQ_WHITESNAKE')
update _Ranking set Total=Total+1000 where charname = @charname
if(@MobName='MOB_ARABIA_KIDEMONAS'
OR @MobName='MOB_MADELYN_1'
OR @MobName='MOB_MADELYN_2'
OR @MobName='MOB_MADELYN_3'
OR @MobName='MOB_MADELYN_4'
OR @MobName='MOB_MADELYN_5'
OR @MobName='MOB_MADELYN_6')
update _Ranking set Total=Total+200 where charname = @charname

if(@MobName='MOB_GM_DEVIN' OR @MobName='MOB_GM_LOOK')
update _Ranking set Total=Total+150 where charname = @charname

where_love2003 · 04/22/2015, 22:07

Any Help here

Syloxx · 04/22/2015, 22:28

TABLE:

Service	Unique	Points
1	MOB_XXX_XXX	100
1	MOB_YYY_YYY	200
1	MOB_ZZZ_ZZZ	300

Code:

DECLARE @CharName varchar(16)
    , @Unique varchar(129)
    , @Points int

SELECT @CharName = charname
    , @Unique = unique_name
FROM inserted

SELECT @Points = Points
FROM _CUSTOM_TABLE
WHERE Unique = @Unique
AND Service = 1

IF @Points <> 0
    UPDATE SRO_CUSTOM_DB.dbo._Ranking
    SET Points += @Points
    WHERE CharName = @CharName

keep in mind to add a check if the killer is charname or nickname

1st wrong datatyped
2nd wrong selection (if you want this selection you have to set the trigger to "AFTER INSERT"
3rd select from the table is useless since there is a "inserted" temp table
4th slow, ugly and shitty query dont even want to read it...

Quote:

Originally Posted by NotEvenMad

My tip;
Go learn difference betweeen INT, Varchar and so on.

Change DECLARE @MobName int;
TO DECLARE @MobName varchar(MAX);

Change DECLARE @charname int;
TO DECLARE @charname varchar(MAX);

my tip:
learn to set the correct datatypes

charname can't be bigger then 16 (related to _Char table) so varchar(16)
same goes for CodeName (related to _RefObjCommon) so varchar(129)

superficial knowledge is useless and very dangerous!

where_love2003 · 04/22/2015, 23:23

Quote:
Originally Posted by Syloxx
TABLE:

Service Unique Points
1 MOB_XXX_XXX 100
1 MOB_YYY_YYY 200
1 MOB_ZZZ_ZZZ 300
Code:
DECLARE @CharName varchar(16)
    , @Unique varchar(129)
    , @Points int

SELECT @CharName = charname
    , @Unique = unique_name
FROM inserted

SELECT @Points = Points
FROM _CUSTOM_TABLE
WHERE Unique = @Unique
AND Service = 1

IF @Points <> 0
    UPDATE SRO_CUSTOM_DB.dbo._Ranking
    SET Points += @Points
    WHERE CharName = @CharName
keep in mind to add a check if the killer is charname or nickname

1st wrong datatyped
2nd wrong selection (if you want this selection you have to set the trigger to "AFTER INSERT"
3rd select from the table is useless since there is a "inserted" temp table
4th slow, ugly and shitty query dont even want to read it...

my tip:
learn to set the correct datatypes

charname can't be bigger then 16 (related to _Char table) so varchar(16)
same goes for CodeName (related to _RefObjCommon) so varchar(129)

superficial knowledge is useless and very dangerous!

First Thanks sir
can you give me more learn to do it work

Syloxx · 04/23/2015, 00:10

Quote:

Originally Posted by where_love2003

First Thanks sir
can you give me more learn to do it work

sorry, i dont have that much time, but you can find everything sql related if you just google.

where_love2003 · 04/23/2015, 00:16

[quote

Code:

DECLARE @CharName varchar(16)
    , @Unique varchar(129)
    , @Points int

SELECT @CharName = charname
    , @Unique = unique_name
FROM inserted

SELECT @Points = Points
FROM _CUSTOM_TABLE
WHERE Unique = @Unique
AND Service = 1

IF @Points <> 0
    UPDATE SRO_CUSTOM_DB.dbo._Ranking
    SET Points += @Points
    WHERE CharName = @CharName

[/quote]

i want only how to use it
in Procedure or Triggers or what ?

i hope to tell me that only

i do it your idea in triggers
like this

Code:

USE [SHARD]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER tgr_unique_name
ON [dbo].[_UniqueKillLog] 

AFTER INSERT 
AS
Begin
DECLARE @CharName varchar(16)
    , @Unique varchar(129)
    , @Points int

SELECT @CharName = charname
    , @Unique = unique_name
FROM inserted 

SELECT @Points = Points
FROM [dbo].[RankPoint] /* your Tab Unique add Point */
WHERE [Unique] = @Unique
AND Service = 1

IF @Points <> 0
    UPDATE Dan_com.dbo._Ranking /* Save Point of Char */
    SET Points += @Points
    WHERE CharName = @CharName
	END
	go

Thanks #Syloxx
fixed
but if char not in dbo._ranking not insert

Thanks to try help me
working fine
#Close