Trigger is not recognizing me the values sent by NEW

Question

Trigger is not recognizing me the values sent by NEW

Navigation

#1 by (0 votes)

0

The fact is that I am making a trigger to act as a mirror in another table, however it is not working as it should. Here I leave the trigger:

CREATE TRIGGER new_loaner_added 
AFTER INSERT ON 'total_loaner' for each row
begin
    INSERT INTO NuevaVisita (not_cliente, not_fecha, not_act, ofi, not_fecha_proxima, not_tipo, not_estado, not_motivo, not_usuarioactu, not_horainicio, not_horafin, not_despacho) VALUES (NEW.idpaciente, CURDATE(), '1', '1', '2017-09-12', '1', '1', 'ALGO', 'NADIE', '09:00', '10:00', '1')
END;

The issue is that the NEW.idpaciente is not recovering and I get a value of 0. With the other fields of the trigger the same thing happens to me whenever I use NEW.field.

I will continue insisting to see if I can find the solution, in the meantime I would appreciate any information that the community can provide me.

mysql trigger

asked by argven tecnology 12.09.2017 в 17:11

source

1 answer

What are the CSS3 prefixes that are currently still required for some properties? Move data between classes

score 0 · Accepted Answer

When creating the trigger does not generate an error ?, in my test, the line generates an error: AFTER INSERT ON 'total_loaner' for each row :

mysql> DELIMITER //

mysql> CREATE TRIGGER 'new_loaner_added'
    -> AFTER INSERT ON 'total_loaner' FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO 'NuevaVisita' ('not_cliente')
    ->   VALUES (NEW.'idpaciente');
    -> END//
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 ''total_loaner' FOR EACH ROW
                    BEGIN
                      INSERT INTO 'NuevaVisita' ('not_cliente')
                      ' at line 2

mysql> DELIMITER ;

After solving the problem, change:

'total_loaner'

for

'total_loaner'

everything works as expected:

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 5.7.11    |
+-----------+
1 row in set (0.00 sec)

mysql> DROP TABLE IF EXISTS 'NuevaVisita', 'total_loaner';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS 'total_loaner' (
    ->   'idpaciente' SERIAL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS 'NuevaVisita' (
    ->   'not_cliente' BIGINT UNSIGNED NOT NULL
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER //

mysql> CREATE TRIGGER 'new_loaner_added'
    -> AFTER INSERT ON 'total_loaner' FOR EACH ROW
    -> BEGIN
    ->   INSERT INTO 'NuevaVisita' ('not_cliente')
    ->   VALUES (NEW.'idpaciente');
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;

mysql> INSERT INTO 'total_loaner' SELECT NULL;
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SELECT 'idpaciente'
    -> FROM 'total_loaner';
+------------+
| idpaciente |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT 'not_cliente'
    -> FROM 'NuevaVisita';
+-------------+
| not_cliente |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

See db-fiddle .