Mysql – executing command prompt command in MySql trigger

MySQLtrigger

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.

Best Answer

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';

Related Solutions

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

Mysql – How to structure IF condition in MySQL trigger

I assume you will value TableB first as it contains the order_no. In that case you need to use an update statement in your trigger instead of an insert statement:

SQL Fiddle

MySQL 5.6.6 m9 Schema Setup:

CREATE TABLE TableA(order_id INT, sku VARCHAR(10));

CREATE TABLE TableB(order_id INT, order_no VARCHAR(10),sku_copy VARCHAR(10));

GO
CREATE TRIGGER trigger_name
    AFTER INSERT ON TableA
    FOR EACH ROW BEGIN

    UPDATE TableB
    SET sku_copy = NEW.sku
    WHERE order_id = NEW.order_id;
END;
GO
INSERT INTO TableB(order_id, order_no)VALUES(1,'111');
INSERT INTO TableB(order_id, order_no)VALUES(2,'222');
INSERT INTO TableB(order_id, order_no)VALUES(3,'333');
GO
INSERT INTO TableA(order_id, sku)VALUES(1,'AAA'),(2,'BBB');

(The GO in this example is used as a batch separator and not send to MySQL.)

Query 1:

SELECT * FROM TableB;

Results:

| ORDER_ID | ORDER_NO | SKU_COPY |
----------------------------------
|        1 |      111 |      AAA |
|        2 |      222 |      BBB |
|        3 |      333 |   (null) |

If you also want to react to updates, just create an AFTER UPDATE trigger like this: