[Share] SQL Trigger Scripts Collections

[smarkies]

All SQL Trigger Scripts will be posted here.
Non-SQL Post will be deleted.
So start posting your (WORKING) SQL Scripts here to be linked in Sticky Threads.

Dont forget to include description.
Please we need your cooperation.

Set Starting Default Level
date: 06-15-2006, 02:52 AM

A LOT of people have been asking how to set the default level of a new character to 100 (or any level). So here is a MORE EFFECTIVE way to do it...

HERE ARE THE STEPS...

1. Open/Login you SQL Server Management Studio
2. Expand the Databases Group
3. Expand the RanGame1 Database
4. Select New Query from the toolbar
5. From the New Query window on the right pane, copy and paste this SQL Trigger Code...

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE TRIGGER [dbo].[NewChar_Level] ON [dbo].[ChaInfo]
after update
as
BEGIN

Update [ChaInfo] set [ChaInfo].ChaLevel=100
From Inserted
Where Inserted.ChaLevel = 1 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaStRemain = 300
From Inserted
Where Inserted.ChaStRemain = 3 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaSkillPoint = 200
From Inserted
Where Inserted.ChaSkillPoint = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

END

6. Execute the SQL Statement.
7. Verify if the SQL Trigger is created by Expanding dbo.ChaInfo tables, expand triggers. If you don't see NewChar_Level trigger under Triggers group, try refreshing your display.

This SQL Trigger has been tested by me and works 100%....

NOTE:
This should serve as your BASIC Template for SQL Triggering... You can do a lot of things with this... You could set the initial Stats, Stat Points, Money, Character Exp, Character Inventory, Character Skills etc. of a NEWLY CREATED CHARACTER... You would just have to be creative and update the SQL Trigger to suit your needs...


I hope this helps you...

















------------------------------------
After waiting for few days, still heavenzel sql is only posted here. Where are the other members who have sql scripts? where is the cooperation here? tsk tsk tsk..

Fine I'll just do it my own digging and posting..

[MentaL]

[heavenzel]

^_~ ok i'll share this although it posted long time ago buahaha

i use this trigger as a part of my maintenance once a week ^^

Just click "NEW QUERY" then copy and paste this green text then execute..

TRIGGER CODE FOR DELETING DELETED CHARACTERS

USE RANGAME1
DELETE FROM Chainfo where ChaDeleted = 1

_________________________________________________________________

TRIGGER CODE FOR DELETING FILE IN RANLOG

USE RanLog
Truncate table "ItemList"
Truncate table "LogAction"
Truncate table "LogExchangeFlag"
Truncate table "LogHackProgram"
Truncate table "LogItemExchange"
Truncate table "LogItemMax"
Truncate table "LogMakeType"
Truncate table "LogServerState"
Truncate table "SKillList"

DONT FORGET TO SHRINK YOUR DATABASE AFTER THAT..

[smarkies]

Auto deletion of inactive account
date: 10-09-2007, 10:25 AM
by: asheron2k6

If you're using SQL 2000
1. Open SQL Enterprise Manager
2. Expand your RAN database
3. Go to Tools > SQL Query Analyzer
4. Copy and paste this script:

 USE RANGAME1
DELETE FROM Chainfo where chadeleted = 1

5. Then click on the "Execute query (F5)" button... or go to Query > Execute

Try this

If you're using SQL 2005:
1. Open SQL Server Management Studio Express
2. Expand your RAN database
3. Click on the "New Query" button
4. Copy and paste this script:

USE RANGAME1
DELETE FROM Chainfo where chadeleted = 1

5. Then click on the "Execute" button... or go to Query > Execute

-----------------------------------------------------------------------------------------
Auto deletion of inactive account
date: 10-09-2007, 11:18 AM
by: sicksand


If you're using SQL 2000
1. Open SQL Enterprise Manager
2. Expand your RAN database
3. Go to Tools > SQL Query Analyzer
4. Copy and paste this script:

 DELETE FROM [UserInfo] WHERE DATEDIFF(day,[UserInfo].LastLoginDate, GETDATE()) >= 30

5. Then click on the "Execute query (F5)" button... or go to Query > Execute

Try this

If you're using SQL 2005:
1. Open SQL Server Management Studio Express
2. Expand your RAN database
3. Click on the "New Query" button
4. Copy and paste this script:

 DELETE FROM [UserInfo] WHERE DATEDIFF(day,[UserInfo].LastLoginDate, GETDATE()) >= 30

5. Then click on the "Execute" button... or go to Query > Execute

[smarkies]

AutoReborn Scripts for SQL 2005
date: 08-14-2007, 12:29 AM
by: Tammy

I am aware that guides already got posted but none was really complete like no one ever said how to limit reborns or how to make you get more stat points each time you reborn

so here it goes :

Open your SQL manager and go to RanGame1 > Tables > ChaInfo

Rightclick dbo.ChaInfo and click Design Table

Go to the bottom of the list that appears
there at the Column name put "ChaReborn"
datatype : Smallint
allow "NULL"

when you're done save the edits and open the tree of dbo.ChaInfo and find "Trigger"
Rightclick Trigger and click "New Trigger"

and put this in :

CREATE TRIGGER [dbo].[Auto_Reborn] ON [dbo].[ChaInfo]
after update
as
BEGIN

Update [ChaInfo] set [ChaInfo].ChaLevel=1, -- Level that you get after reborn
[ChaInfo].ChaReborn=[ChaInfo].ChaReborn + 1, -- Reborn counter
[ChaInfo].ChaExp=0, -- Set Exp back to 0
[ChaInfo].ChaStRemain = (100*[ChaInfo].ChaReborn), -- how many stat points you get per reborn
[ChaInfo].ChaPower = 0, -- Set the number of Pow you added to 0
[ChaInfo].ChaStrong = 0, -- Set the number of Vit you added to 0
[ChaInfo].ChaStrength = 0, -- Set the number of Stm you added to 0
[ChaInfo].ChaSpirit = 0, -- Set the number of Int you added to 0
[ChaInfo].ChaDex = 0, -- Set the number of Dex you added to 0
[ChaInfo].ChaMoney = [ChaInfo].ChaMoney - 2000000, -- Optional : Reborn cost 2m
[ChaInfo].ChaSkillPoint = (10*[ChaInfo].ChaReborn), -- Set the Skillpoints per reborn
[ChaInfo].ChaSkills = NULL, -- Optional : Forget all skills after reborn
[ChaInfo].ChaSkillSlot = NULL, -- Optional : Delete shortcut list of skills
[ChaInfo].ChaQuest = NULL -- Optional : Delete quest list (need restart all quests) after reborn
From Inserted
Where Inserted.ChaLevel = 300 -- Level to reborn
and [ChaInfo].ChaReborn < 10 -- Max Reborn number
and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum
and [ChaInfo].ChaBright >= 0 -- Optional : Require 0 or more attr for reborn
and [ChaInfo].ChaMoney >= 2000000 -- Optional : Require 2m gold for reborn

End

How to make double reborn :

Change the first script a bit (at the lvs)

 Update [ChaInfo] set [ChaInfo].ChaLevel=1,  -- Level that you get after reborn
[ChaInfo].ChaReborn=[ChaInfo].ChaReborn + 1, -- Reborn counter
[ChaInfo].ChaExp=0,  -- Set Exp back to 0
[ChaInfo].ChaStRemain = (100*[ChaInfo].ChaReborn), -- how many stat points you get per reborn
[ChaInfo].ChaPower = 0, -- Set the number of Pow you added to 0
[ChaInfo].ChaStrong = 0, -- Set the number of Vit you added to 0
[ChaInfo].ChaStrength = 0, -- Set the number of Stm you added to 0
[ChaInfo].ChaSpirit = 0, -- Set the number of Int you added to 0
[ChaInfo].ChaDex = 0, -- Set the number of Dex you added to 0
[ChaInfo].ChaMoney = [ChaInfo].ChaMoney - 2000000, -- Optional : Reborn cost 2m
[ChaInfo].ChaSkillPoint = (10*[ChaInfo].ChaReborn), -- Set the Skillpoints per reborn
[ChaInfo].ChaSkills = NULL, -- Optional : Forget all skills after reborn
[ChaInfo].ChaSkillSlot = NULL, -- Optional : Delete shortcut list of skills
[ChaInfo].ChaQuest = NULL -- Optional : Delete quest list (need restart all quests) after reborn
From Inserted
 Where Inserted.ChaLevel >= 290 -- Level to 1x reborn
and [ChaInfo].ChaLevel  <  295  -- Limitation of 1x Reborn
and [ChaInfo].ChaReborn < 10 -- Max Reborn number
and [ChaInfo].ChaName=inserted.ChaName 
and [ChaInfo].UserNum =inserted.UserNum
and [ChaInfo].ChaBright >= 0 -- Optional : Require 0 or more attr for reborn
and [ChaInfo].ChaMoney >= 2000000 -- Optional : Require 2m gold for reborn

and then add below this :

Update [ChaInfo] set [ChaInfo].ChaLevel=1,  -- Level that you get after reborn
 [ChaInfo].ChaReborn=[ChaInfo].ChaReborn + 2, -- Reborn counter -- NO need to change anything beside of THIS you get automatically more stat points if you put the reborn counter +2
[ChaInfo].ChaExp=0,  -- Set Exp back to 0
[ChaInfo].ChaStRemain = (100*[ChaInfo].ChaReborn), -- how many stat points you get per reborn
[ChaInfo].ChaPower = 0, -- Set the number of Pow you added to 0
[ChaInfo].ChaStrong = 0, -- Set the number of Vit you added to 0
[ChaInfo].ChaStrength = 0, -- Set the number of Stm you added to 0
[ChaInfo].ChaSpirit = 0, -- Set the number of Int you added to 0
[ChaInfo].ChaDex = 0, -- Set the number of Dex you added to 0
 [ChaInfo].ChaMoney = [ChaInfo].ChaMoney - 4000000, -- Optional : Reborn cost 4m
[ChaInfo].ChaSkillPoint = (10*[ChaInfo].ChaReborn), -- Set the Skillpoints per reborn
[ChaInfo].ChaSkills = NULL, -- Optional : Forget all skills after reborn
[ChaInfo].ChaSkillSlot = NULL, -- Optional : Delete shortcut list of skills
[ChaInfo].ChaQuest = NULL -- Optional : Delete quest list (need restart all quests) after reborn
From Inserted
 Where Inserted.ChaLevel >= 296 -- Level to 2x reborn
and [ChaInfo].ChaReborn < 10 -- Max reborn number
and [ChaInfo].ChaName=inserted.ChaName 
and [ChaInfo].UserNum =inserted.UserNum
and [ChaInfo].ChaBright >= 0 -- Optional : Require 0 or more attr for reborn
 and [ChaInfo].ChaMoney >= 4000000 -- Optional : Require 4m gold for double reborn

after that press the Red "!" to perform the compiling of the Trigger, and you're done!
To Reborn : Change Map / Relog

Note : To delete any of the changes when reborn like Skills delete, just delete that line and don't forget that ONLY the last thing to happen got NO comma at the end, any line needs a comma beside the last otherwise it won't work

You can use any value of your char to get changed or as requirement for reborn
even require to be in a certain Map with
[ChaInfo].ChaSaveMap

or even to have a certain guild nick (like nick : REBORN) to make ppl reborn at will and not by mistake
[ChaInfo].ChaGuName = 'REBORN'
To clear the guild nick after reborn add this line : [ChaInfo].ChaGuName = ' ' -- careful its 2 ' not 1 "
and don't forget the comma if its not the last thing to change

Note : If you need REBORN then Reborn will NOT work, its case sensitive and don't forget the '

Important : Don't try around too much with this code on an open server, if you change things wrong it could end up in making EVERY char of your server reborn each second without having to satisfy and requirement, please back up your server files first or make it on a separate server to avoid hard damage while trying to modify this code

Example for fatal mistake :

DO NOT USE THIS

Update [ChaInfo] set [ChaInfo].ChaLevel=1
Update [ChaInfo] set [ChaInfo].ChaReborn=[ChaInfo].ChaReborn + 1, -- Reborn counter
Update [ChaInfo] set [ChaInfo].ChaStRemain = (100*[ChaInfo].ChaReborn)
Update [ChaInfo] set [ChaInfo].ChaMoney = [ChaInfo].ChaMoney -2000000
Where Inserted.ChaLevel = 300 -- Level to reborn
and [ChaInfo].ChaMoney > 2000000

THIS would make EVERY char in your server reborn for free every time they relog or move to other map!!!
Yea for FREE no -2m because the condition of lv 300 and 2m gold is ONLY for the last Update command, means you would have made this skirpt :
Change map or relog = Reborn
and
If Lv 300 and money 2m then Money -2m

Edit : Edited it now I'm using a variable that is 0 when you make your char and stays 0 all the way, its not used by the game so I use it to make ChaReborn get 0 instead of NULL
but if anyone knows how to check if a variable is NULL tell me and I'll add it instead, cos if I make
Where Inserted.ChaReborn = NULL
it won't do anything, it will just cause your char to rollback any time instead of reborn X.x

------------------------------------------------------------------------

Rollback only comes up if your HP exceeds 32767.5 if you have 32767 hp its fine if you got 32768 hp you will rollback.

To add stats how? By GMcharEdit? Or Reborn?

By Reborn :

Replace this line :

[ChaInfo].ChaStRemain = (100*[ChaInfo].ChaReborn), -- how many stat points you get per reborn

with this :

[ChaInfo].ChaStRemain = 10000

With GMCharEditor :

How to use it :

Open SQL Manager go to RanUser database and go Server Group, open it and fill in :
SGNum : 1
SGName : the way you want call your server
OdbcName : RanGame1
OdbcUserID : the user ID you used (Default : sa)
OdbcPassword : the password you used (Default : 1234)
OdbcLogName : RanLog
OdbcLogUserID : the user ID you used (Default : sa)
OdbcLogPassword : the password you used (Default : 1234)

After that go to ServerInfo and fill it in like this :

SGNum : 1
SvrNum : 1
SvrType : 4

Then Run GMCharEdit and fill the fields

First field : RanUser
Second field : The Username you typed at SQL manager
Third field : The Password you typed at SQL manager
Fourth field : The Password you typed at SQL manager

then click ok, chose your server from the list and it will open the GM char editor

--------------------------------------------------------------------------

The problem is that the SQL refuses to change anything on the database having 2 triggers its like he doesn't know what to do so to avoid damage doesn't do anything at all.

Solution : Fusion both Trigger into 1

add this into your first trigger

at the bottom

before "END"

Update [ChaInfo] set [ChaInfo].ChaReborn = 0,[ChaInfo].ChaTribe = 1
from Inserted
Where Inserted.ChaTribe = 0

Update [ChaInfo] set [ChaInfo].ChaLevel=1, -- Level that you get after reborn
[ChaInfo].ChaReborn=[ChaInfo].ChaReborn + 1, -- Reborn counter
[ChaInfo].ChaExp=0, -- Set Exp back to 0
[ChaInfo].ChaStRemain = (100*[ChaInfo].ChaReborn), -- how many stat points you get per reborn
[ChaInfo].ChaPower = 0, -- Set the number of Pow you added to 0
[ChaInfo].ChaStrong = 0, -- Set the number of Vit you added to 0
[ChaInfo].ChaStrength = 0, -- Set the number of Stm you added to 0
[ChaInfo].ChaSpirit = 0, -- Set the number of Int you added to 0
[ChaInfo].ChaDex = 0, -- Set the number of Dex you added to 0
[ChaInfo].ChaMoney = [ChaInfo].ChaMoney - 2000000, -- Optional : Reborn cost 2m
[ChaInfo].ChaSkillPoint = (10*[ChaInfo].ChaReborn), -- Set the Skillpoints per reborn
[ChaInfo].ChaSkills = NULL, -- Optional : Forget all skills after reborn
[ChaInfo].ChaSkillSlot = NULL, -- Optional : Delete shortcut list of skills
[ChaInfo].ChaQuest = NULL -- Optional : Delete quest list (need restart all quests) after reborn
From Inserted
Where Inserted.ChaLevel = 300 -- Level to reborn
and [ChaInfo].ChaReborn < 10 -- Max Reborn number
and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum
and [ChaInfo].ChaBright >= 0 -- Optional : Require 0 or more attr for reborn
and [ChaInfo].ChaMoney >= 2000000 -- Optional : Require 2m gold for reborn

then it should work
I guess you mean this trigger :

like I said just add before the "END" like this :

CREATE TRIGGER [dbo].[NewChar_Level] ON [dbo].[ChaInfo]
after update
as
BEGIN

Update [ChaInfo] set [ChaInfo].ChaLevel=100
From Inserted
Where Inserted.ChaLevel = 1 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaSkillPoint=1500
From Inserted
Where Inserted.ChaSkillPoint = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaStRemain=1500
From Inserted
Where Inserted.ChaStRemain = 3 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaMoney=1000000
From Inserted
Where Inserted.ChaMoney = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum



Update [ChaInfo] set [ChaInfo].ChaPower=100
From Inserted
Where Inserted.ChaPower = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaStrong=100
From Inserted
Where Inserted.ChaStrong = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaStrength=100
From Inserted
Where Inserted.ChaStrength = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaSpirit=100
From Inserted
Where Inserted.ChaSpirit = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaDex=100
From Inserted
Where Inserted.ChaDex = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

Update [ChaInfo] set [ChaInfo].ChaIntel=100
From Inserted
Where Inserted.ChaIntel = 0 and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum

-- HERE

Update [ChaInfo] set [ChaInfo].ChaReborn = 0,[ChaInfo].ChaTribe = 1
from Inserted
Where Inserted.ChaTribe = 0

Update [ChaInfo] set [ChaInfo].ChaLevel=1, -- Level that you get after reborn
[ChaInfo].ChaReborn=[ChaInfo].ChaReborn + 1, -- Reborn counter
[ChaInfo].ChaExp=0, -- Set Exp back to 0
[ChaInfo].ChaStRemain = (100*[ChaInfo].ChaReborn), -- how many stat points you get per reborn
[ChaInfo].ChaPower = 0, -- Set the number of Pow you added to 0
[ChaInfo].ChaStrong = 0, -- Set the number of Vit you added to 0
[ChaInfo].ChaStrength = 0, -- Set the number of Stm you added to 0
[ChaInfo].ChaSpirit = 0, -- Set the number of Int you added to 0
[ChaInfo].ChaDex = 0, -- Set the number of Dex you added to 0
[ChaInfo].ChaMoney = [ChaInfo].ChaMoney - 2000000, -- Optional : Reborn cost 2m
[ChaInfo].ChaSkillPoint = (10*[ChaInfo].ChaReborn), -- Set the Skillpoints per reborn
[ChaInfo].ChaSkills = NULL, -- Optional : Forget all skills after reborn
[ChaInfo].ChaSkillSlot = NULL, -- Optional : Delete shortcut list of skills
[ChaInfo].ChaQuest = NULL -- Optional : Delete quest list (need restart all quests) after reborn
From Inserted
Where Inserted.ChaLevel = 300 -- Level to reborn
and [ChaInfo].ChaReborn < 10 -- Max Reborn number
and [ChaInfo].ChaName=inserted.ChaName
and [ChaInfo].UserNum =inserted.UserNum
and [ChaInfo].ChaBright >= 0 -- Optional : Require 0 or more attr for reborn
and [ChaInfo].ChaMoney >= 2000000 -- Optional : Require 2m gold for reborn

END

[janesantana16]

Where i can fine a SQL trigger scripts in autoreborn with lvl 207 using SQL2000 because i dont know how to use SQL2005

Thankz advance

By:[GameMaster]

[smarkies]

READ FROM THE TOP!!

[KaTribu]

Auto deletion of inactive account
date: 10-09-2007, 11:18 AM
by: sicksand

If you're using SQL 2005:
1. Open SQL Server Management Studio Express
2. Expand your RAN database
3. Click on the "New Query" button
4. Copy and paste this script:

 DELETE FROM [UserInfo] WHERE DATEDIFF(day,[UserInfo].LastLoginDate, GETDATE()) >= 30

5. Then click on the "Execute" button... or go to Query > Execute

I tried using this script to delete inactive user accounts but I keep getting the following error:
Invalid object name 'Userinfo'

Guys, any suggestions?

[smarkies]

I suggest you make a thread regarding with your problem.

This is a SQL Trigger Scripts Collection section.