Trigger insert and change data

Question

Good afternoon, I am trying to create a Trigger that after entering data(after Insert) in the table prodvdmat, it goes to the "input" table and checks if there is data in it, if there is no creates a new record, if there is updates(amount and value).

--

-- Acionadores `prodvdmat`

--

DROP TRIGGER IF EXISTS `trg_entradarm`;

DELIMITER //

CREATE DEFINER=`root`@`localhost` TRIGGER `bailledados`.`trg_entradarm` AFTER INSERT ON `prodvdmat` FOR EACH ROW
BEGIN  
  declare codV int;
  declare codA int;  
  select codVendedor, codArmar INTO codV, codA from prodvdmat;
  IF (codV <> new.codVendedor and codA <> new.codArmar) then
     insert into entradarm set codVendedor = new.codVendedor, codArmar = new.codArmar, quantidade = new.quantidade, valor = new.valor;
  else
    UPDATE entradarm SET quantidade = quantidade + new.Quantidade, valor = valor + new.valor WHERE codVendedor = new.codVendedor and codArmar = new.codArmar;
  end if;
END//

DELIMITER ;

You’re making this mistake:

Operation failed: There was an error while Applying the SQL script to the database.

ERROR 1172: 1172: Result consisted of more than one Row

SQL Statement:

INSERT INTO `bailledados`.`prodvdmat` (`codVendedor`, `codArmar`, `quantidade`, `valor`, `datacompra`) VALUES ('1', '1', '4', '25.00', '2019-01-14 00:00:00')


CREATE DATABASE IF NOT EXISTS `bailledados` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `bailledados`;

-- --------------------------------------------------------

--
-- Estrutura da tabela `entradarm`
--

CREATE TABLE IF NOT EXISTS `entradarm` (
  `codEntra` int(11) NOT NULL AUTO_INCREMENT,
  `codVendedor` int(11) NOT NULL DEFAULT '0',
  `codArmar` int(11) NOT NULL DEFAULT '0',
  `quantidade` int(11) NOT NULL DEFAULT '0',
  `valor` decimal(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`codEntra`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=5 ;

--
-- Extraindo dados da tabela `entradarm`
--

INSERT INTO `entradarm` (`codEntra`, `codVendedor`, `codArmar`, `quantidade`, `valor`) VALUES
(1, 1, 1, 6, '21.90'),
(2, 5, 3, 5, '10.00'),
(3, 1, 1, 2, '10.00'),
(4, 0, 0, 0, '0.00');

-- --------------------------------------------------------

--
-- Estrutura da tabela `prodvdmat`
--

CREATE TABLE IF NOT EXISTS `prodvdmat` (
  `codPrdvdmat` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
  `codVendedor` int(11) NOT NULL,
  `codArmar` int(11) NOT NULL,
  `quantidade` int(11) NOT NULL,
  `valor` decimal(10,2) NOT NULL,
  `datacompra` datetime NOT NULL,
  PRIMARY KEY (`codPrdvdmat`),
  KEY `FK_prodvdmat_codVendedor` (`codVendedor`),
  KEY `FK_prodvdmat_codArmar` (`codArmar`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;

--
-- Extraindo dados da tabela `prodvdmat`
--

INSERT INTO `prodvdmat` (`codPrdvdmat`, `codVendedor`, `codArmar`, `quantidade`, `valor`, `datacompra`) VALUES
(0000000004, 5, 5, 1, '10.80', '2018-11-25 00:00:00'),
(0000000005, 5, 6, 1, '19.80', '2018-11-25 00:00:00'),
(0000000006, 6, 7, 6, '89.40', '2018-11-25 00:00:00'),
(0000000007, 6, 8, 6, '6.00', '2018-11-25 00:00:00'),
(0000000008, 9, 9, 1, '24.90', '2018-10-21 00:00:00'),
(0000000012, 1, 1, 4, '11.90', '2018-11-09 00:00:00'),
(0000000013, 4, 4, 2, '5.20', '2018-11-25 00:00:00'),
(0000000014, 5, 3, 5, '10.00', '2018-12-15 00:00:00'),
(0000000015, 5, 3, 2, '10.00', '2019-01-09 00:00:00'),
(0000000017, 1, 1, 2, '5.00', '2019-01-09 00:00:00');

--
-- Limitadores para a tabela `prodvdmat`
--
ALTER TABLE `prodvdmat`
  ADD CONSTRAINT `FK_prodvdmat_codArmar` FOREIGN KEY (`codArmar`) REFERENCES `armarinhos` (`codArmar`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `FK_prodvdmat_codVendedor` FOREIGN KEY (`codVendedor`) REFERENCES `vendedores` (`codVendedor`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Executing:

INSERT INTO `bailledados`.`prodvdmat` (`codVendedor`, `codArmar`, `quantidade`, `valor`, `datacompra`) VALUES ('1', '1', '4', '25.00', '2019-01-14 00:00:00');

Answer 1

Try it this way:

DROP TRIGGER IF EXISTS trg_entradarm;
DELIMITER //

CREATE DEFINER=root@localhost TRIGGER bailledados.trg_entradarm 
AFTER INSERT ON prodvdmat 
FOR EACH ROW
BEGIN
    SET @existe =   (
                        SELECT  1 
                        FROM    entradarm 
                        WHERE   codVendedor = new.codVendedor 
                            AND codArmar    = new.codArmar
                    );

    IF IFNULL(@existe, 0) = 0 THEN
        INSERT  INTO entradarm(codVendedor, codArmar, quantidade, valor)
        VALUES  (new.codVendedor, new.codArmar, new.quantidade, new.valor)
    ELSE
        UPDATE  entradarm 
        SET     quantidade  = quantidade + new.Quantidade
            ,   valor       = valor + new.valor 
        WHERE   codVendedor = new.codVendedor 
            AND codArmar    = new.codArmar;
    END IF;
END//

DELIMITER ;