MySQL Trigger to automatic insert based on date

Question

i tried following example .but it doesn't insert. is any problem in where clause??

CREATE DEFINER = `root`@`localhost` TRIGGER `new` AFTER INSERT ON `employee`
FOR EACH
ROW INSERT INTO employee_log( first_name, last_name )
SELECT first_name, last_name
FROM employee
WHERE start_date = now( )

Triggers are executed when something occurs. Insert/delete or update statements for instance. If you want something to occur based on time you can schedule an event. — Mad Dog Tannen
– Mad Dog Tannen, Commented Jul 11, 2013 at 7:55
yes! we are inserting data into daily_plan table. based on the date row need to copy today_plan table also — 9pixle
– 9pixle, Commented Jul 11, 2013 at 8:03
The code above drops the table, that would drop the triggers assigned to it. You need a different approach to make this work efficent. — Mad Dog Tannen
– Mad Dog Tannen, Commented Jul 11, 2013 at 8:10
Probably because Now( ) should be now() is not the same value as it was when it was inserted. The trigger runs once after a employee hsa been inserted. the value now(0 changes probably by miliseconds but it wont be the same. unless you use sort desc in your where clause, you can use the value new.id() instead. Did you actually read about triggers as adviced? — Mad Dog Tannen
– Mad Dog Tannen, Commented Jul 12, 2013 at 16:09

mirkobrankovic · Accepted Answer · 2013-07-11 08:01:40Z

1

I'm not sure if that can be done with triggers. But you can make a shell script, and execute mysql commands

test.sh:

#!/bin/bash
#delete everything from today_plan
mysql -e "DELETE FROM today_plan" --user=user_name --password=your_password db_name
#insert only todays plans to today_plan
mysql -e "INSERT INTO today_plan (pjtno, pattern, itemno, belt, kiln, qty, lnusr, distant,  remark, decww, plandate, nowdate, shipment) SELECT pjtno, pattern, itemno, belt, kiln, qty, lnusr, distant,  remark, decww, plandate, nowdate, shipment FROM daily_plan WHERE plandate=NOW()" --user=user_name --password=your_password db_name
exit

then add execution of shell script as cron job in /etc/crontab like:

0  6 * * * root php /path/to/script/./test.sh

Which will execute it every morning at 6:00

ps. This is just one idea of how to get it done :D

edit: Or you can make mysql event

edited Jul 11, 2013 at 8:01

answered Jul 11, 2013 at 7:47

mirkobrankovic

2,3471 gold badge22 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mad Dog Tannen Over a year ago

I would even schedule an event in mysql (make sure event_scheduler=1). Then all the logic can be put into this event, or even assign a store procedure to it, so you wont have to amend the event all the time.

Mad Dog Tannen Over a year ago

:D , now that event, happends alot, if only there was a trigger for missing something in the answer post hehe

mirkobrankovic Over a year ago

heheeh feel free to edit answer with mysql event example and I'll approve it :)

Mad Dog Tannen · Accepted Answer · 2013-07-12 16:11:38Z

"A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table"

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Create a stored procedure

    CREATE PROCEDURE yourdatabase.sp_plandate_process ()

    BEGIN
    // here you write the code to process the data
    END

Create the event

CREATE EVENT mysql.name_of_event
ON SCHEDULE
  EVERY 24 HOUR STARTS '2013-07-10 06:00:00'
COMMENT 'Write comment'
DO
CALL sp_plandate_process();

Make sure that your variable event_scheduler=1 , if its set to 0 you can just do

SET event_scheduler=1;

And this will start the connection for the event scheduler.

Since you changed the question i edit again

New answer:

Probably because Now( ) should be now() is not the same value as it was when it was inserted. The trigger runs once after a employee hsa been inserted. the value now(0 changes probably by miliseconds but it wont be the same. unless you use sort desc in your where clause, you can use the value new.id() instead. Did you actually read about triggers as adviced? – Kay Nelson 41 secs ago

Collectives™ on Stack Overflow

MySQL Trigger to automatic insert based on date

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related