Error creating a trigger with MySql

Question

Error creating a trigger with MySql

Navigation

#1 by (0 votes)

0

Good, I am trying to make a trigger with MySql, the problem is that I have always worked with Sql-Server and now I am having some complications. From the following code what is wrong with me? :

USE 'test_db';
DELIMITER 
$$
CREATE TRIGGER 'tr_insert_customer' AFTER INSERT ON purchases FOR EACH ROW
BEGIN 
    DECLARE @usuario varchar(30), @accion varchar(6), @customer varchar(8)
    SET @usuario = (SELECT Cus_User FROM INSERTED)
    SET @accion = 'creado';
    SET @customer = (SELECT Cus_Id FROM INSERTED)
    INSERT INTO his_cus (HCu_Action, HCu_User, HCu_Cus_Id) VALUES(@accion,@usuario,@customer)
END;
$$

When trying to create it (with phpMyAdmin) the following error occurs: #1064 - Algo está equivocado en su sintax cerca '@usuario varchar(30), @accion varchar(6), @customer varchar(8)SET @usuario' en la linea 5

What the trigger does in case it helps is to obtain the data of INSERT and insert them in another table as a history of user actions

mysql trigger

asked by gmarsi 15.06.2017 в 15:28

source

1 answer

Return the sorted data in a dataset and display them in a form I have an error when trying to open a python program in the cmd

score 0 · Answer 1

only 04 details for you to solve these problems:

1.- It is not necessary to declare variables.

DECLARE @user varchar (30), @accion varchar (6), @customer varchar (8)

2.- Some delimiters need to be closed ";" by T-SQL

3.- You have to close the SP Delimiter at the end.

DELIMITER;

4.- In MySQL, the values (new. for insertion) and (old. to update or delete) for the triggers are used; in your case you will have to use the new.

SET @user = (SELECT new.Cus_User FROM purchases);

Your code will run like this

DELIMITER $$
CREATE TRIGGER 'tr_insert_customer' AFTER INSERT ON purchases FOR EACH ROW
BEGIN 
    SET @usuario = (SELECT new.Cus_User FROM purchases);
    SET @accion = 'creado';
    SET @customer = (SELECT new.Cus_Id FROM purchases);
    INSERT INTO his_cus (HCu_Action, HCu_User, HCu_Cus_Id) VALUES(@accion,@usuario,@customer);
END; $$ 
DELIMITER ;

Good luck, you tell me how it went.