CODEWITHSUNDEEP
mysqltriggers
9pixle
9pixle

Reputation: 556

MySQL Trigger to automatic insert based on date

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( )

Upvotes: 1

Views: 1551

Answers (2)

Mad Dog Tannen
Mad Dog Tannen

Reputation: 7244

"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

Upvotes: 1

mirkobrankovic
mirkobrankovic

Reputation: 2347

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

Upvotes: 1

Related Questions