· 7 years ago · Jan 14, 2019, 06:34 PM
1--
2
3-- Acionadores `prodvdmat`
4
5--
6
7DROP TRIGGER IF EXISTS `trg_entradarm`;
8
9DELIMITER //
10
11CREATE DEFINER=`root`@`localhost` TRIGGER `bailledados`.`trg_entradarm` AFTER INSERT ON `prodvdmat` FOR EACH ROW
12BEGIN
13 declare codV int;
14 declare codA int;
15 select codVendedor, codArmar INTO codV, codA from prodvdmat;
16 IF (codV <> new.codVendedor and codA <> new.codArmar) then
17 insert into entradarm set codVendedor = new.codVendedor, codArmar = new.codArmar, quantidade = new.quantidade, valor = new.valor;
18 else
19 UPDATE entradarm SET quantidade = quantidade + new.Quantidade, valor = valor + new.valor WHERE codVendedor = new.codVendedor and codArmar = new.codArmar;
20 end if;
21END//
22
23DELIMITER ;
24
25INSERT INTO `bailledados`.`prodvdmat` (`codVendedor`, `codArmar`, `quantidade`, `valor`, `datacompra`) VALUES ('1', '1', '4', '25.00', '2019-01-14 00:00:00')
26
27
28CREATE DATABASE IF NOT EXISTS `bailledados` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
29USE `bailledados`;
30
31-- --------------------------------------------------------
32
33--
34-- Estrutura da tabela `entradarm`
35--
36
37CREATE TABLE IF NOT EXISTS `entradarm` (
38 `codEntra` int(11) NOT NULL AUTO_INCREMENT,
39 `codVendedor` int(11) NOT NULL DEFAULT '0',
40 `codArmar` int(11) NOT NULL DEFAULT '0',
41 `quantidade` int(11) NOT NULL DEFAULT '0',
42 `valor` decimal(10,2) NOT NULL DEFAULT '0.00',
43 PRIMARY KEY (`codEntra`)
44) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT AUTO_INCREMENT=5 ;
45
46--
47-- Extraindo dados da tabela `entradarm`
48--
49
50INSERT INTO `entradarm` (`codEntra`, `codVendedor`, `codArmar`, `quantidade`, `valor`) VALUES
51(1, 1, 1, 6, '21.90'),
52(2, 5, 3, 5, '10.00'),
53(3, 1, 1, 2, '10.00'),
54(4, 0, 0, 0, '0.00');
55
56-- --------------------------------------------------------
57
58--
59-- Estrutura da tabela `prodvdmat`
60--
61
62CREATE TABLE IF NOT EXISTS `prodvdmat` (
63 `codPrdvdmat` int(10) unsigned zerofill NOT NULL AUTO_INCREMENT,
64 `codVendedor` int(11) NOT NULL,
65 `codArmar` int(11) NOT NULL,
66 `quantidade` int(11) NOT NULL,
67 `valor` decimal(10,2) NOT NULL,
68 `datacompra` datetime NOT NULL,
69 PRIMARY KEY (`codPrdvdmat`),
70 KEY `FK_prodvdmat_codVendedor` (`codVendedor`),
71 KEY `FK_prodvdmat_codArmar` (`codArmar`)
72) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;
73
74--
75-- Extraindo dados da tabela `prodvdmat`
76--
77
78INSERT INTO `prodvdmat` (`codPrdvdmat`, `codVendedor`, `codArmar`, `quantidade`, `valor`, `datacompra`) VALUES
79(0000000004, 5, 5, 1, '10.80', '2018-11-25 00:00:00'),
80(0000000005, 5, 6, 1, '19.80', '2018-11-25 00:00:00'),
81(0000000006, 6, 7, 6, '89.40', '2018-11-25 00:00:00'),
82(0000000007, 6, 8, 6, '6.00', '2018-11-25 00:00:00'),
83(0000000008, 9, 9, 1, '24.90', '2018-10-21 00:00:00'),
84(0000000012, 1, 1, 4, '11.90', '2018-11-09 00:00:00'),
85(0000000013, 4, 4, 2, '5.20', '2018-11-25 00:00:00'),
86(0000000014, 5, 3, 5, '10.00', '2018-12-15 00:00:00'),
87(0000000015, 5, 3, 2, '10.00', '2019-01-09 00:00:00'),
88(0000000017, 1, 1, 2, '5.00', '2019-01-09 00:00:00');
89
90--
91-- Limitadores para a tabela `prodvdmat`
92--
93ALTER TABLE `prodvdmat`
94 ADD CONSTRAINT `FK_prodvdmat_codArmar` FOREIGN KEY (`codArmar`) REFERENCES `armarinhos` (`codArmar`) ON DELETE CASCADE ON UPDATE CASCADE,
95 ADD CONSTRAINT `FK_prodvdmat_codVendedor` FOREIGN KEY (`codVendedor`) REFERENCES `vendedores` (`codVendedor`);
96
97/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
98/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
99/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
100
101INSERT INTO `bailledados`.`prodvdmat` (`codVendedor`, `codArmar`, `quantidade`, `valor`, `datacompra`) VALUES ('1', '1', '4', '25.00', '2019-01-14 00:00:00');