I’m learning about triggers and events and I have the following issue:
I have created a trigger that inserts data in a table called updated_movie
when an update is made in table movie
.
Also, I want to create an event that executes every day at 11:30am that creates a text file with some data from the table updated_movie
. I have made this so far:
delimiter !!
drop event if exists createFile !!
create event createFile on schedule at "11:30"
do begin
declare path varchar(255) default "/Users/Shared/BDD/L19/";
declare nameFile varchar(255) default curdate();
declare done int default 0;
declare t varchar(255);
-- creation of the text file?
declare c cursor for select title from updated_movie;
declare continue handler for not found set done = 1;
open c;
l:loop
fetch c into t;
if done = 1 then
leave l;
end if;
call copyIntoFile(t, nameFile);
end loop l;
end!!
delimiter ;
This is the event I want to be executed every day. How can I create a text file into the path declared in the event with the same file name as the declared variable nameFile
?
Also, procedure copyIntoFile
looks like this so far:
delimiter !!
drop procedure if exists copyIntoFile !!
create procedure copyIntoFile(in str varchar(255), in fileName varchar(255)
begin
-- Copy into the text file?
end !!
delimiter ;
How can I make it so I can insert some data into the text field?
In case that you are wondering, table updated_movie
just have one varchar(255)
field.
Advertisement
Answer
CREATE EVENT createFile
ON SCHEDULE
EVERY 1 DAY
STARTS CURRENT_DATE + INTERVAL '11:30' HOUR_MINUTE
ENABLE
DO
SELECT *
INTO OUTFILE 'drive:\folder\filename.ext'
FROM updated_movie
WHERE created_at >= NOW() - INTERVAL 1 DAY;
Modify conditions, output expressions, add export specifications if needed.
Check secure_file_priv
setting and related ones, and justify destination forder accordingly. FILE privilege needed.
PS. BEGIN-END, DELIMITER, etc. – are excess.