[Guide] Ways to use database triggers.

[Arrakis]

Triggers you can imagine as guards for the table. If an insert/update or delete knocks on the table door, the guards decide if they are allowed to enter the table, and in which shape.

You can tell triggers to react on what dml (data manipulation language), and how to deal with it. For example, before a row is inserted into a table, i want to do some checks. This could be for example if the charactername is longer then 3 characters, if not then refuse.

There are 3 DML statements you can set triggers to listen to, beeing update, insert and delete. There are also 2 occasions you can let them fire of ... before the action occurs, or after.


What is the major benefit of using them? For one, ensuring data consistency and integrity. This trigger will ALWAYS fire, no matter from what tool you try to manipulate the data. Could be a website creating an account, ascent writing data or somebody entering data with Heidi or Navicat.


Now, for some practical uses.



Example, giving a character level 70.

create trigger logon.bir_characters # name it
before insert on logon.characters               # react when, on what dml and for which table
for each row
begin
    set new.level=70;                                   # set the new value for level
end;

Easy huh ? ... we can complicate it a bit.
I also want characters to always have at least 10G when they log in.

create trigger logon.bir_characters
before insert on logon.characters
for each row
begin
   set new.level=70;
   if new.gold < 10000 then              # you have less then ten gold
      set new.gold=10000;                        # if so, then make it 10 again :)
  end if;
end;

Next step ... we want to give him exalted reputation, all flightpoints, and 61 talent points ( and smoe more nice to haves).
There is a catch here. Ascent never updates a line in tables, it always deletes and reinserts them, so it will always fire "ON INSERT" trigger everytime you logon, or create a character.
But ... if you already logged in with your character, and spended your 61 points, you will get them again next time you login, as the trigger can't tell the difference between a new character or existing one. There are some ways to get around that though.

create trigger logon.bir_characters
before insert on logon.characters
for each row
begin
  if new.xp=0 then
    set new.level=70;
    set new.available_talent_points=61;   
    set new.xp = 1;
    set new.current_hp=10000;
    set new.current_power=10000;
    set new.reputation = '1,0,0,6000,21,1,500,100000,46,0,0,0,47,1,-42000,100000,54,1,-42000,100000,59,1,0,100000,67,0,3500,3500,68,1,3100,100000,69,1,-42000,100000,70,2,-10000,-10000,72,1,-42000,100000,76,1,500,100000,81,1,500,100000,83,0,2999,2999,86,0,2999,2999,87,1,-6500,100000,92,0,2000,2000,93,0,2000,2000,169,0,500,500,270,1,0,100000,289,0,0,0,349,0,0,0,369,1,500,100000,469,2,-42000,-42000,470,1,500,100000,471,2,-42000,-42000,509,1,-42000,100000,510,1,0,100000,529,1,200,100000,530,1,500,100000,549,0,2999,2999,550,0,2999,2999,551,0,2999,2999,569,0,0,0,570,0,0,0,571,0,0,0,574,0,0,0,576,1,-3500,100000,577,1,500,100000,589,1,-42000,100000,609,0,0,0,729,1,0,100000,730,1,-42000,100000,749,1,0,100000,809,0,0,0,889,1,0,100000,890,1,-42000,100000,892,0,0,0,909,1,0,100000,910,1,-42000,100000,911,1,4000,100000,922,1,0,100000,930,1,-42000,100000,932,1,-3500,100000,933,1,0,100000,934,1,3500,100000,935,1,0,100000,936,0,0,0,941,1,-500,100000,942,1,0,100000,946,1,-42000,100000,947,1,0,100000,967,1,0,100000,970,1,-2500,100000,978,1,-42000,100000,989,1,0,100000,990,1,0,100000,1005,0,3000,3000,1011,1,0,100000,1012,1,0,100000,1015,1,-42000,100000,1031,1,0,100000,1038,1,0,100000,';
    set new.exploration_data='4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,';
    set new.taxiMask='4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 ';
   end if;
end;

Now, the trick is .. i made it conditional. If you create a new character, your xp will be 0. The trigger sees that, update lot's of nice columns, and also tp to 61 .... and then it sets xp to 1 ... making sure that next time the trigger fires, it knows it's not a new character anymore, and it does not need to touch it.



You can make it as hard as you want ... the next one levels your pet up, sets happiness to fullest, and loyalty to 6.

For the more advanced:

create trigger logon.bir_playerpets
before insert on logon.playerpets
for each row
begin
if substring_index(substring_index(new.fields,' ' , 35) ,' ',-1) != 70 then
  set new.fields =concat( substring_index(new.fields,' ' , 27), ' 999999 ' , substring_index(new.fields,' ',-( 232 - 27) ) ) ;
  set new.fields =concat( substring_index(new.fields,' ' , 34), ' 69 ' , substring_index(new.fields,' ',-( 232 - 34) ) ) ;
  set new.fields =concat( substring_index(new.fields,' ' , 159), ' 1536 ' , substring_index(new.fields,' ',-( 232 - 159) ) ) ;
  set new.fields =concat( substring_index(new.fields,' ' , 162), ' 75049 ' , substring_index(new.fields,' ',-( 232 - 162) ) ) ;
  set new.fields =concat( substring_index(new.fields,' ' , 163), ' 75050 ' , substring_index(new.fields,' ',-( 232 - 163) ) ) ;
end if;
end;

Background ... the settings for pets are stored in the fields column. This column contains a string consisting out of 233-ish values. I know by disecting the UpdateFields.h ( and help from SB@L at ascentemu) what each value is representing, so i can manipulate the string.
You can see me setting happiness to 999999 ... level to 69 and also current and max Hp for that level. 1536 (binary shift) set loyalty to 6.

The reason i set level to 69, is because ascent caches a lot of information from the database, and will not go back to the database for refreshing data until you logout. Also using a trigger on a table level, will often not trigger certain pieces of functionality in the core. As you levelup a pet, a function is called in ascent to also update the pet stats ... it's not when we do it via the back-end. So to get around that, I've set the level to 69, and only needing 1 more xp to go to the next level.
After you've tamed a pet, you logout and back in to refresh the cache, and then you kill 1 mob, to get the last bit of XP to go to 70 .. and have all correct stats for your pet ... and loyalty 6

I know ... that was a dirty one, but it works without having to recompile core code, and there is no need to restart the server. That's another nice feature of triggers, you can change them on the fly, and they are active immediatly.

One more thing ... you drop triggers with the command: "Drop trigger if exists <triggername>"

I hope this little guide is usefull to the database-players here




ps: If you start screaming blasphemy, copyrights and all those things ... I am MarlonB on ac-web

[dakna]

Question, how would I reset the reputations back to defaults? like I wanted to get rid of the rep trigger.

thx

[Arrakis]

what you could do is
- remove it from the trigger ... or drop the whole trigger
- create new char
- copy the reputation value from that character and use it to update the other characters for example

[dakna]

what you could do is
- remove it from the trigger ... or drop the whole trigger
- create new char
- copy the reputation value from that character and use it to update the other characters for example

ok newb question I know, its alrdy set as a trigger. Is there a way I could get rid of it by removing the trigger? if thats possible. or Do I have to get ingame values from a new char


thx

[Arrakis]

you can remove the "set new.reputation" from the trigger.
Drop the trigger, and then recreate it without that line.

Characters that were made during the period this trigger was active will have everything exalted and need to be manually corrected if that is what you want.

[dakna]

kk, how do I drop a trigger

thx, sorry for being a bit new on this.

[Cursed]

Very nice m8 3x rep

[Arrakis]

kk, how do I drop a trigger

thx, sorry for being a bit new on this.

No problem ... it shows me the guide is lacking in some areas


"Drop trigger if exists <triggername>"

(Also added that to guide).

[dakna]

No problem ... it shows me the guide is lacking in some areas


"Drop trigger if exists <triggername>"

(Also added that to guide).

TY ty id give rep if I could

[dakna]

Drop trigger if exists logon.bir_characters
create trigger logon.bir_characters
before insert on logon.characters
for each row
begin
if new.xp=0 then
set new.level=70;
if new.gold < 10000000 then # you have less then ten gold
new.gold=10000000; # if so, then make it 10 again
set new.available_talent_points=61;
set new.xp = 1;
set new.current_hp=10000;
set new.current_power=10000;
set new.exploration_data='4294967295,4294967295,4294967295,4294967295,4294967295,429 4967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,429496 7295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,429496729 5,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4 294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294 967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967 295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295 ,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,42 94967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,42949 67295,';
set new.taxiMask='4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 ';
end if;
end;

so that script should remove the current one and replace with the new 1?

[Arrakis]

yes .... don't forget a semicolon ';' after the drop command....

Also remove the if gold < then line .... you are using it wrong.

[dakna]

Alright thx so this should do it? sorry for being full of questions. I just wanna make sure tis 100% right

drop trigger if exists logon.bir_characters;
create trigger logon.bir_characters
before insert on logon.characters
for each row
begin
if new.xp=0 then
set new.level=70;
new.gold=10000000; # if so, then make it 10 again
set new.available_talent_points=61;
set new.xp = 1;
set new.current_hp=10000;
set new.current_power=10000;
set new.exploration_data='4294967295,4294967295,4294967295,4294967295,4294967295,429 4967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,429496 7295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,429496729 5,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4 294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294 967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967 295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295 ,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,42 94967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,42949 67295,';
set new.taxiMask='4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 ';
end if;
end;

[Arrakis]

Everywhere you have a "set" before the column ... except for gold ... fix that, and it's fine. Also remove the comment line after it (# .....)

[dakna]

Everywhere you have a "set" before the column ... except for gold ... fix that, and it's fine. Also remove the comment line after it (# .....)

so take out all of the "sets"

drop trigger if exists logon.bir_characters;
create trigger logon.bir_characters
before insert on logon.characters
for each row
begin
if new.xp=0 then
new.level=70;
new.gold=10000000;
new.available_talent_points=61;
new.xp = 1;
new.current_hp=10000;
new.current_power=10000;
new.exploration_data='4294967295,4294967295,4294967295,4294967295,4294967295,429 4967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,429496 7295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,429496729 5,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4 294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294 967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967 295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295 ,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,42 94967295,4294967295,4294967295,4294967295,4294967295,4294967295,4294967295,42949 67295,';
new.taxiMask='4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 ';
end if;
end;

[Arrakis]

no m8 ... the opposite ... you are indeed set(ting) a new(.) column ... but you forgot the one for gold.