MySQL Trigger. - Printable Version

MySQL Trigger. - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+--- Thread: MySQL Trigger. (/showthread.php?tid=408174)

MySQL Trigger. - Roel - 17.01.2013

Hello,

I made a trigger which runs when the third cheatlog get inserted of a player.
So when a player has been detected three times for cheating he will get banned.
this is the trigger:

PHP код:



CREATE TRIGGER `check_for_ban` BEFORE INSERT ON `log_cheat`

 FOR EACH ROW IF ((SELECT COUNT(*) FROM `log_cheat` WHERE PLR_ID = NEW.PLR_ID AND TRIGGERED != 1) > 2) 

THEN INSERT INTO `log_banned` (`PLR_ID`,`PLR_NAME`,`PLR_IP`,`ADM_ID`,`ADM_IP`, `DESCRIPTION`, `TIME`, `TYPE`) 

VALUES (NEW.PLR_ID, (SELECT PLR_NAME FROM `plr_account` WHERE plr_account.PLR_ID = NEW.PLR_ID), NEW.PLR_IP, -1, 'none', '3 times kicked for using cheats.', NOW(), 0);

END IF

The only problem is, when I unban this person, he get directly banned after one cheatlog, and i don't want to delete all the cheatlogs when he got unbanned.
So I made a column called 'TRIGGERED' inside log_cheat.
I want this to set on 1 for all rows that belows to the player that is getting banned, so basicly it will be:

PHP код:



UPDATE `log_cheat` SET TRIGGERED=1 WHERE PLR_ID = NEW.PLR_ID

So this will looks like:

PHP код:



CREATE TRIGGER `check_for_ban` BEFORE INSERT ON `log_cheat`

 FOR EACH ROW IF ((SELECT COUNT(*) FROM `log_cheat` WHERE PLR_ID = NEW.PLR_ID AND TRIGGERED != 1) > 2) 

THEN INSERT INTO `log_banned` (`PLR_ID`,`PLR_NAME`,`PLR_IP`,`ADM_ID`,`ADM_IP`, `DESCRIPTION`, `TIME`, `TYPE`) 

VALUES (NEW.PLR_ID, (SELECT PLR_NAME FROM `plr_account` WHERE plr_account.PLR_ID = NEW.PLR_ID), NEW.PLR_IP, -1, 'none', '3 times kicked for using cheats.', NOW(), 0);

UPDATE `log_cheat` SET TRIGGERED=1 WHERE PLR_ID = NEW.PLR_ID;

END IF

The only problem is that it gives me an error when it runs, because it cannot update the table log_cheat because it is stored inside the trigger or something, someone knows a way to modify the trigger so this will work?

Re: MySQL Trigger. - 3ventic - 17.01.2013

You could add dates to the log_cheat and log_banned and after unban set a column to indicate the ban is no longer active in log_banned. Then you could check if there are 3 log_cheat entries after the date of last ban. Another possibility is to copy the banned players' cheat logs into another table and remove them from the original, essentially keeping them both but resetting the counter.

Re: MySQL Trigger. - Roel - 17.01.2013

Well thanks, but the banned player row in log_banned get's deleted when a player gets unbanned, so there is no way to check if the banned data when the player get banned again for cheats.
And yes i can make a another table and copy the logs to there, but I'm wondering if delete and move does works inside the trigger since update doesnt work too, ofcourse I can test this but creating to cheat tables is a bit circuitous since I have to modify the ucp tables and other things.
But thanks for giving me some options.

Re: MySQL Trigger. - Roel - 17.01.2013

Problem resolved, I will make a trigger in log_banned so when a row get deleted I will set his cheat log rows to triggered, so the next time it doesnt count them anymore.