SQL: Survivor History

[linkinx]

So yes, the triggers keep getting deleted, not sure why.

 

[Doc]

Run it as a query, I had to remove the delimeter part, also you might want to change dayz'@'% to be dayz'@'localhost if the account only has localhost access.

Yes, if you do not change it to localhost, it may crash your server (I learnt the hard way). I really ought to read the code before I ctrl+c ctrl + v rofl

 

[Peep]

Yeah sorry about that we ran a separate server for MySQL at the time and that was one of the things I forgot to change.

I will update the OP since most people are running user dayz from localhost.

 

[Doc]

Thank you, it works great .It's worth noting that those using a vanilla pwnoZ0r pack will have to modify the code to get it to work as the user/host are different. You should be able to avoid delimiters completely for it so this should be suitable (all content original to Peep but this works for me):

CREATE TRIGGER `proc_insertHistory` AFTER INSERT ON `character_data` FOR EACH ROW insert into character_history
 
    values(NEW.PlayerUID, NEW.CharacterID, NEW.Inventory, NEW.Backpack, NEW.currentState, NEW.Medical, NEW.Worldspace, NEW.Alive, 'insert', now());
 
CREATE TRIGGER `proc_updateHistory` AFTER UPDATE ON `character_data` FOR EACH ROW begin
 
    insert into character_history
 
    values(NEW.PlayerUID, NEW.CharacterID, NEW.Inventory, NEW.Backpack, NEW.currentState, NEW.Medical, NEW.Worldspace, NEW.Alive, 'update', now()); --
 
end;
 
CREATE TRIGGER `proc_deleteHistory` AFTER DELETE ON `character_data` FOR EACH ROW insert into character_history
 
    values(OLD.PlayerUID, OLD.CharacterID, OLD.Inventory, OLD.Backpack, OLD.currentState, OLD.Medical, OLD.Worldspace, OLD.Alive, 'delete', now());

 

[Excelsior]

The proc_deleteHistory trigger seems to not be doing anything at all, any suggestions?

 

[Peep]

Looks like I made a mistake in the OP, this is actually the deletion event:

CREATE DEFINER=`dayz`@`localhost` EVENT `event_cleanHistory` ON SCHEDULE EVERY 4 HOUR STARTS '2013-01-25 15:47:10' ON COMPLETION NOT PRESERVE ENABLE DO delete from survivor_history where date_sub(now(), interval 3 day) > `timestamp`

Edit it as you wish.

 

[Snow]

hey man, so this code, saves players inventorys every 4 hours and deletes after 3 days? also what does the ON COMPLETION NOT PRESERVE ENABLE, do?

​