Update de saldo anterior

[brino 0]

Galera do iMasters,

 

Estou montando uma tabela de fluxo de caixa, se alguém puder ajudar numa dúvida agradeço.

 

Tenho a tabela fluxo de caixa.

DROP TABLE IF EXISTS `db_brino`.`tb_fxc`;
CREATE TABLE  `db_brino`.`tb_fxc` (
 `id_fxc` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `cont_id` int(1) unsigned NOT NULL DEFAULT '0',
 `data_fxc` date NOT NULL,
 `desc_fxc` varchar(45) NOT NULL,
 `valor_fxc` decimal(10,2) NOT NULL,
 `tipo_fxc` tinyint(1) unsigned NOT NULL DEFAULT '0',
 `saldo_fxc` decimal(10,2) NOT NULL,
 PRIMARY KEY (`id_fxc`),
 KEY `FK_cont_has_contas_id` (`cont_id`),
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Segue o exemplo da tabela:

 

INSERT INTO tb_fxc VALUES (null, 1, curdate(), 'Saldo inicial', 5000, 0, 5000);
INSERT INTO tb_fxc VALUES (null, 2, curdate(), 'Compra', 300, 1, 300);

Resultado:
id    conta    data    desc           valor    tipo    saldo
1     1        ----    Saldo inicial  5000,00  0       5000,00
2     2        ----    Compra         300,00   1       300,00

Minha dúvida esta no seguinte, a cada insert quero que uma Trigger faça o seguinte: de acordo com o lançamento, será somado ou subtraído do saldo anterior no saldo atual.

 

Esperado:

 

id    conta    data    desc           valor    tipo    saldo
1     1        ----    Saldo inicial  5000,00  0       5000,00
2     2        ----    Compra         300,00   1       4700,00

Saldo atual = Saldo anterior +ou- Saldo atual.

 

UPDATE tb_fxc
SET saldo_fxc = saldo_fxc +ou- (SELECT saldo_fxc FROM tb_fxc WHERE id_fxc = (SELECT max(id_fxc-1) FROM tb_fxc))
WHERE id_fxc = (SELECT max(id_fxc-1) FROM tb_fxc)

No caso o update que estou fazendo fica apontando o seguinte erro:

You can't specify target table 'tb_fxc' for update in FROM clause

[DiegoAngra07 21]

Boa noite,

 

Veja: http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause

 

Você não pode usar a tabela que está dando UPDATE como parte de uma subquery, conforme esta documentação.

 

Faça como o colega sugeriu no link. Espero que seja útil, abraço.

[brino 0]

Cara fiz da seguinte maneira, mas continua dando o mesmo erro.

UPDATE tb_fxc AS a
 INNER JOIN tb_fxc AS b ON a.id_fxc = (SELECT max(id_fxc) FROM tb_fxc) AND b.id_fxc = (SELECT max(id_fxc-1) FROM tb_fxc)
 SET a.saldo_fxc = b.saldo_fxc - a.saldo_fxc
 WHERE a.id_fxc = 2

 

Não sei ao certo, mas acho que seria mais ou menos isso.

[Motta 645]

Um problema comum, use variáveis auxiliares para a operação.

 

Exemplo em SqlServer

 

Busca no Google de outros possíveis.

[brino 0]

Obrigado pelas referências até aqui, mas ainda não estou conseguindo fazer o update.

No meu caso estou utilizando a mesma tabela para fazer o update, que no caso não é aceito pela documentação.

Mas não estou conseguindo fazer funcionar.

[DiegoAngra07 21]

Bom dia,

 

Não conseguiu com as variáveis como indicado pelo Motta? Poste o seu código com variáveis. Eu não entendi o porque de uma das suas condições ser: AND b.id_fxc = (SELECT max(id_fxc-1) FROM tb_fxc). Não entendi aquele -1, ele não devia estar fora dos parenteses?

[brino 0]

Bom dia, Diego

 

Cara estou fazendo de tantas maneiras que agora já estou meio sem noção de como fazer.

Mas minha ideia é a seguinte. Quando for inserido um novo registro será feito uma atualização no ultimo registro inserido, pegando o registro anterior e o atual da mesma tabela.

No caso do (SELECT max(id-1) FROM tabela), pelo que vi tanto faz usar dentro ou fora dos parenteses o -1.

 

Acho que você conseguiu entender a ideia, poderia mostrar uma luz nesse update.

 

Obrigado

[DiegoAngra07 21]

Bom dia, Diego

 

Cara estou fazendo de tantas maneiras que agora já estou meio sem noção de como fazer.

Mas minha ideia é a seguinte. Quando for inserido um novo registro será feito uma atualização no ultimo registro inserido, pegando o registro anterior e o atual da mesma tabela.

No caso do (SELECT max(id-1) FROM tabela), pelo que vi tanto faz usar dentro ou fora dos parenteses o -1.

 

Acho que você conseguiu entender a ideia, poderia mostrar uma luz nesse update.

 

Obrigado

Vejamos se eu entendi, com este meu exemplo:

 

DROP TABLE IF EXISTS `testeSaldo`;

CREATE  TABLE `testeSaldo` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `descricao` VARCHAR(45) NULL ,
 `valor` DECIMAL(10,2) NULL ,
 `saldo` DECIMAL(10,2) NULL ,
 PRIMARY KEY (`id`) 
) ENGINE = InnoDB;

INSERT INTO `testeSaldo` (`descricao`, `valor`, `saldo`) VALUES ('Teste 1', 500, 500);

DELIMITER $$

CREATE TRIGGER `beforeInsertTesteSaldo` BEFORE INSERT ON `testeSaldo` 
FOR EACH ROW
BEGIN
   DECLARE novoSaldo DECIMAL(10,2);

   SET novoSaldo = (SELECT ts.saldo FROM testeSaldo ts ORDER BY id DESC LIMIT 1);

   SET NEW.saldo = NEW.valor + novoSaldo;
END $$

DELIMITER ;

INSERT INTO `testes`.`testeSaldo` (`descricao`, `valor`) VALUES ('Teste 2', 300);

1 - Criei a tabela

 

2 - Inseri um registro

 

3 - Criei a Trigger como BEFORE (faça BEFORE UPDATE também no seu caso se for necessário), ao meu ver é o ideal ao invés de AFTER INSERT / UPDATE.

 

4 - Na Trigger digo que o saldo do novo registro será o saldo anterior, somado ao valor do novo registro (no seu caso somado ou subtraído, faça essa implementação).

 

5 - Inseri um novo registro de teste, que ficará com saldo 800 (NEW.valor + Saldo anterior).

 

É mais ou menos isso? Faça ajustes se necessários para o seu caso. Espero que ajude, qualquer coisa manda aí, abraço.

 

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

 

Adicionando:

 

Evite usar o -1, pois na teoria o ID deve sim ser um registro sequencial sem falhas, mas na prática nem sempre será assim.

 

Se você tiver uma tabela e inserir 3 registros, eles ficarão com IDs 1, 2 e 3. Se por algum motivo X alguma rotina da aplicação ou banco excluiu o registro 2 porque ele tinha um erro, no momento que você tentar incluir um quarto registro (ID 4), usar o MAX(id) - 1 iria te retornar 2 (o MAX valeria 3, subtraindo um ficaria 2), que já não existe mais na sua tabela.

 

Não sei se fui claro quanto ao exemplo, espero que sim.

 

Abraço.

[brino 0]

Vejamos se eu entendi, com este meu exemplo:

 

DROP TABLE IF EXISTS `testeSaldo`;

CREATE  TABLE `testeSaldo` (
 `id` INT NOT NULL AUTO_INCREMENT ,
 `descricao` VARCHAR(45) NULL ,
 `valor` DECIMAL(10,2) NULL ,
 `saldo` DECIMAL(10,2) NULL ,
 PRIMARY KEY (`id`) 
) ENGINE = InnoDB;

INSERT INTO `testeSaldo` (`descricao`, `valor`, `saldo`) VALUES ('Teste 1', 500, 500);

DELIMITER $$

CREATE TRIGGER `beforeInsertTesteSaldo` BEFORE INSERT ON `testeSaldo` 
FOR EACH ROW
BEGIN
   DECLARE novoSaldo DECIMAL(10,2);

   SET novoSaldo = (SELECT ts.saldo FROM testeSaldo ts ORDER BY id DESC LIMIT 1);

   SET NEW.saldo = NEW.valor + novoSaldo;
END $$

DELIMITER ;

INSERT INTO `testes`.`testeSaldo` (`descricao`, `valor`) VALUES ('Teste 2', 300);

1 - Criei a tabela

 

2 - Inseri um registro

 

3 - Criei a Trigger como BEFORE (faça BEFORE UPDATE também no seu caso se for necessário), ao meu ver é o ideal ao invés de AFTER INSERT / UPDATE.

 

4 - Na Trigger digo que o saldo do novo registro será o saldo anterior, somado ao valor do novo registro (no seu caso somado ou subtraído, faça essa implementação).

 

5 - Inseri um novo registro de teste, que ficará com saldo 800 (NEW.valor + Saldo anterior).

 

É mais ou menos isso? Faça ajustes se necessários para o seu caso. Espero que ajude, qualquer coisa manda aí, abraço.

 

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

 

Adicionando:

 

Evite usar o -1, pois na teoria o ID deve sim ser um registro sequencial sem falhas, mas na prática nem sempre será assim.

 

Se você tiver uma tabela e inserir 3 registros, eles ficarão com IDs 1, 2 e 3. Se por algum motivo X alguma rotina da aplicação ou banco excluiu o registro 2 porque ele tinha um erro, no momento que você tentar incluir um quarto registro (ID 4), usar o MAX(id) - 1 iria te retornar 2 (o MAX valeria 3, subtraindo um ficaria 2), que já não existe mais na sua tabela.

 

Não sei se fui claro quanto ao exemplo, espero que sim.

 

Abraço.

 

Cara,

 

Consegui aqui, esta funcionando muito bem. Eu é que estava complicado demais aqui.

Ficou assim

 

DELIMITER $$

CREATE TRIGGER saldoAtual BEFORE INSERT ON tb_fxc
FOR EACH ROW
BEGIN
 DECLARE saldoAnterior DECIMAL(10,2);

 SET saldoAnterior = (SELECT f.saldo_fxc FROM tb_fxc f ORDER BY f.id_fxc DESC LIMIT 1);

 IF (NEW.tipo_fxc = 0) THEN
   SET NEW.saldo_fxc = saldoAnterior + NEW.valor_fxc;
 ELSE
   SET NEW.saldo_fxc = saldoAnterior - NEW.valor_fxc;
 END IF;

END $$

DELIMITER ;

 

Muito obrigado e abraço

[DiegoAngra07 21]

Opa, ficou bem mais simples mesmo.

 

Satisfeito em ajudar, abraço.

[Motta 645]

Atente que talvez seja necessário tratar o UPDATE e o DELETE.

 

Talvez outras duas triggers devam ser criadas (pelo que vi o MySQl) não permite mais de um "verbo" nas troggers.