Can i run a python script on my raspberry pi when a trigger is triggered in Mysql database server?
Database triggers are for DELETE, INSERT, UPDATE sql queries. You cannot trigger a python script with it.
At some point you set the database field to 0 or to 1 with an UPDATE SQL query.
The event based approach is to run the according python script before or after you performed the sql update query. Python could run as a webserver on the pi and you can send values via GET or POST.   
The polling approach is to query the database every x (mili)second with a python script or similar and execute turn on/off functions accordingly.
I'm woriking on a project that checks if LEDSTATUS from a database has the value "0" then led is off, if the value is "1" led is on.
I searched and found out that there is something called UDF that can run scripts but am not sure if it is possible if the database is not local (on a server).
is it possible? if yes how?
I created two python scripts one that turn led on and the other one to turns it off. 
I will create a database with one table LEDSTATUS and will create a trigger that will run when ever the value of LEDSTATUS gets changed, if the value is 0 then run python script that turn led off, and if the value is 1 run the other script.