2

I have one table called table1. In it there are two fields, let say field1 and field2. What I am trying to do is that, when there are records inserted into table1, the values of field1 and field2 get stored in one text file called abc.txt which is on c drive.

I want to do it by calling the command prompt command which is called inside table1's After insert trigger called trigger1.

I have done it in SQL Server but I need it using MySQL. Here is the SQL Server trigger:

create trigger [dbo].[trigger1] on [dbo].[table1] 
for insert

As 

Declare @field_1 varchar(50),@field_2 int,@cmd varchar(250);

select @field_1=i.field1 from inserted i;
select @field_2=i.field2 from inserted i;

PRINT @field_1
Print @field_2;

SET @cmd= 'echo ' + @field_1+','+cast(@field_2 as varchar)+ '>C:\abc.txt'
EXEC Master..xp_cmdshell @cmd

The MySQL trigger that I have written is:

delimiter #
create trigger trigger1 after insert on table1 
for each row
begin
Declare field_1 varchar(50);
Declare field_2 int(50);
Declare cmd varchar(200);

SET field_1 = NEW.field1;
SET field_2  = NEW.field2;

System echo field_1 > C:\abc.txt
end#
delimiter ;

After executing this command it gives an error as

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'echo
field1 > C:\abc.txt
end' at line 11

I am new to MySQL. Maybe I am doing it wrong. Any help is appreciated.

2

system is one of MySQL's internal commands, interpreted by the mysql command-line tool rather than by the SQL engine, as stated in the manual:

mysql sends each SQL statement that you issue to the server to be executed. There is also a set of commands that mysql itself interprets. [...]

system    (\!) Execute a system shell command.

[...]

You can't run the system command inside a stored program (and that includes a trigger), because the entire body of the stored program is sent to the server and the server won't recognise system.

However, you can get an output file with SELECT ... INTO OUTFILE FILE:

  SELECT field_1 INTO OUTFILE '/tmp/abc.txt'
  FIELDS TERMINATED BY ',' 
  LINES TERMINATED BY '\n';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.