1

There is a way to write data to disk from a trigger into a file? The file name need to be created dynamically.

Using PREPARE will not work on trigger. e.g. 

SET @Query1 = 
CONCAT("SELECT ",'data'," INTO DUMPFILE 'c:/temp/", UNIX_TIMESTAMP(),"-",RAND(), ".txt'");
PREPARE statementCreate FROM @Query1;
EXECUTE statementCreate;
DEALLOCATE PREPARE statementCreate;

Note: data is a variable

Or there is other solution to append to the same file from the trigger? Will work that too.

makim
  • 3,154
  • 4
  • 30
  • 49
Adrian Moldovan
  • 443
  • 1
  • 7
  • 15

2 Answers2

6

MySQL documentation says:

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Thus, stored functions and triggers cannot use dynamic SQL (where you construct statements as strings and then execute them).

So no - you can't write data to file which name is dynamically created from a trigger.

Since you can't use dynamic sql but you also approve single file name and append data to it you can load data to temporary file, append results of the query and then output full results to a file. Of course it will work only if there is no other need to use dynamically created query except file name.

On example:

DELIMITER $$

CREATE TRIGGER rewrite_file AFTER INSERT tableName
BEGIN

  CREATE TEMPORARY TABLE IF NOT EXISTS temp(
    pk BIGINT
  );

  LOAD DATA INFILE 'c:/somefile.txt' INTO TABLE temp
  LINES TERMINATED BY '\n';

  INSERT INTO temp (pk) SELECT primaryKeyField FROM tableName;

  SELECT pk INTO OUTFILE 'c:/somefile.txt'
  LINES TERMINATED BY '\n'
  FROM temp;

END$$

DELIMITER ;

It will append file c:/somefile.txt on every insert. Of course this solution sucks if your file grows to millions of entries.

MKB
  • 281
  • 2
  • 14
  • Already did and is not working. That store procedure need to comply to the same restrictions as the trigger. – Adrian Moldovan Sep 12 '13 at 13:05
  • From what I know constructions such as `SET @var = 'SELECT * INTO OUTFILE...' PREPARE... EXECUTE...` can be done from stored procedure. Maybe problem lies elsewhere? Permissions maybe? Problem with query itself? What error do you have? – MKB Sep 12 '13 at 13:25
  • yes, such a query can be done from a stored procedure. This is not the problem here. Running the stored procedure separately works perfect. The problem is when is called from a trigger. The error is "Dynamic SQL is not allowed in stored function or trigger" – Adrian Moldovan Sep 12 '13 at 13:35
  • The question is if there is a way to write data to file from a trigger when the file name need to be created dynamically. Or if is possible to append data from a trigger to a file. – Adrian Moldovan Sep 12 '13 at 13:39
  • I've edited my answer. If it should work depends on your needs which aren't fully revealed from code you provided. – MKB Sep 12 '13 at 14:05
  • The idea is to write something on a file on any insert. e.g. `SELECT 1 INTO DUMPFILE 'c:/temp/a.txt';` both solution are accepted for this purpose: > 1. each call to write in a different file > 2. all calls to write into the same file. – Adrian Moldovan Sep 12 '13 at 14:20
  • Everything depends what is this 'something' you want to write. If you can select it without using dynamically generated query you can achieve your goal by solution I proposed. If your query have to be concatenated from string literals and some parameters before execution then I'm run out off suggestions. – MKB Sep 12 '13 at 14:35
  • sorry..but I just don't follow the solution. That 'something' is the id (primary key) get from trigger. Put an example of your solution. – Adrian Moldovan Sep 12 '13 at 14:43
  • Your example is not working, can't be install on DB because of errors, but not this is the problem. There are 2 main issues there: 1. `LOAD DATA INFILE` is not allowed on stored procedure or triggers. This error is displayed `Error Code : 1314 LOAD DATA is not allowed in stored procedures` 2. `SELECT pk INTO OUTFILE 'c:/somefile.txt' LINES TERMINATED BY \n' FROM temp;` will work only once, because if the file exist can't be overwritten. This error is displayed: `Error Code : 1086 File 'd:/somefile.txt' already exists` – Adrian Moldovan Sep 13 '13 at 20:00
  • Well, code wasn't tested. If SELECT INTO OUTFILE can't overwrite a file and LOAD DATA can't be placed in a trigger and if trigger disallow dynamic queries even hidden in procedure I'm not only run out of ideas. I'm also pretty sure that it is impossible to accomplish your goal by MySQL alone. – MKB Sep 13 '13 at 22:03
4

The solution is: MySQL UDF:http://www.mysqludf.org. I install sys_exec function and after that from trigger I have something like SET @ret_val = sys_exec(CONCAT('echo.>d:/temp/',NEW.id, '-', UNIX_TIMESTAMP() , '.txt')); This will create a empty file for each insert. Tested on windows and linux(CentOS).

Adrian Moldovan
  • 443
  • 1
  • 7
  • 15