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.
Easy huh ? ... we can complicate it a bit.Code: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;
I also want characters to always have at least 10G when they log in.
Next step ... we want to give him exalted reputation, all flightpoints, and 61 talent points ( and smoe more nice to haves).Code: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;
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.
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.Code: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;
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:
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.Code: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;
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