I am doing a trigger in mysql that deletes a record from a table and executes when data is inserted in that table I do it in the following way
DELIMITER $$
CREATE TRIGGER 'comp01'.'ELiminar***'
AFTER INSERT ON 'comp01'.'<ft_inv_slvta>'
BEGIN
DELETE FROM comp01.ft_inv_slvta WHERE codref = '***'
END$$
DELIMITER ;
But I'm having problems with the syntax of the code
When you execute it, this message is displayed
Several points to consider:
FOR EACH ROW . See 14.1.20 CREATE TRIGGER Syntax . CREATE TRIGGER 'comp01'.'ELiminar***'
AFTER INSERT ON 'comp01'.'<ft_inv_slvta>'
FOR EACH ROW
BEGIN
...
Optionally, you can delete the BEGIN ... END , required when executing multiple statements.
Missing ; at end of statement DELETE .
You can not act ( INSERT / UPDATE / DELETE ) on the same table that generates the trigger (trigger), you will get the error:
Can't update table '<ft_inv_slvta>' in stored function/trigger
because it is already used by statement which invoked this stored function/trigger.
See example in db-fiddle .
You can do something like this:
CREATE TRIGGER 'nombre_del_trigger' AFTER INSERT ON 'tu_tabla'
FOR EACH ROW BEGIN
SET @A = 1; //variable x puedes meter una consulta o x valor
IF (NEW.campo IS NOT NULL) THEN
set @variable1 := NEW.id; //Estos son los valores del nuevo registro que se insertó
set @variable2 := NEW.campo;
DELETE FROM tu_tabla WHERE id = @variable1;
END IF;
END;
It worked for me in the following way:
CREATE TRIGGER 'comp01'.'ELiminar***'
AFTER INSERT ON 'comp01'.'ft_inv_slvta'
FOR EACH ROW DELETE FROM comp01.ft_inv_slvta WHERE codref = '***'