Trigger em mysql

[Marisa Lima 0]

Boas.

Preciso criar uma trigger que faz vários inserts numa tabela. O que eu pretendo é que depois de inserir dados numa determinada tabela a trigger me faça um select a outra e obtenha o id de todos os utilizadores activos e insira numa tabela nova. Fiz da seguinte forma mas dá erro ao executar:

CREATE TRIGGER insert_momento_avaliacao_colaborador

AFTER INSERT on momento_avaliacao

FOR EACH ROW

BEGIN

declare id_colaborador int;

declare id_momento_avaliacao int;

 

Select id into id_colaborador from colaborador where activo='0';

 

Select id into id_momento_avaliacao from momento_avaliacao where id=new.id;

 

Insert into momento_avaliacao_colaborador(id_colaborador,id_momento_avaliacao) Values(id_colaborador,id_momento_avaliacao);

 

END

 

o erro é o seguinte: Result consisted of more than one row

Se me poderem ajudar!

[DiegoAngra07 21]

Boa tarde senhorita,

 

declare id_colaborador int;
declare id_momento_avaliacao int;

Select id into id_colaborador from colaborador where activo='0';

Select id into id_momento_avaliacao from momento_avaliacao where id=new.id;

As variáveis declaradas são int, porém você está tentando inserir o resultado de uma consulta inteira nelas. Ou seja, inserir muitas linhas de retorno em uma única variável, como se ela fosse um array.

[Marisa Lima 0]

Obrigada por responder tão depressa. Percebi o que voçê quis dizer, mas como é que eu faço para inserir um array?

No mysql não existe o tipo de dados array! Eu sei que tenho de inserir muitos registos em simultaneo pois o primeiro select retorna 100 e tal registos.

Será que me pode ajudar?

[DiegoAngra07 21]

Claro.

 

Já trabalhou com cursores? Vou te dar um exemplo aqui:

 

DELIMITER $$

CREATE PROCEDURE exemploCursor()
BEGIN
   DECLARE varIdTabela2 int;
   DECLARE f int DEFAULT 0;
   DECLARE cursorExemplo CURSOR FOR SELECT id FROM tabela1;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET f = 1;

   OPEN cursorExemplo ;

   REPEAT
       FETCH cursorExemplo INTO varIdTabela2 ;

       INSERT INTO tabela2 (id) VALUES (varIdTabela2);
   UNTIL (f = 1) END REPEAT ;

   CLOSE cursorExemplo ;
END $$

Explicações:

 

- Linha 1: Declaramos uma variável int que será usada pra armazenar o resultado da consulta.

- Linha 2: Declaramos uma variável int, com DEFAULT 0 (ou seja, já começa como zero), que servirá como controlador do laço REPEAT

- Linha 3: Declaramos um CURSOR, uma variável que armazena uma consulta toda de 1 campo (eu costumo usar para 1 campo apenas, acho que não é possível fazer para mais de 1 no mesmo cursor). Nesse caso pesquisamos o campo id na tabela1.

- Linha 4: Essa linha, resumidamente, serve para dizermos que quando não houver mas resultados no cursor (FOR NOT FOUND), é para a variável f receber o valor 1. Se você notar, nosso REPEAT acontece até que f = 1, sendo assim ele vai parar no momento que não houverem mais resultados no CURSOR.

 

Daí pra frente é moleza, nós abrimos o cursor com o OPEN e iniciamos o laço. A cada looping damos um FETCH do resultado da consulta na nossa variável, e inserimos ela na tabela2. E assim sucessivamente até que não haja mais resultados no cursor. Ao final de tudo nós encerramos ele com o CLOSE.

 

Pense no cursor como um objeto que armazena o resultado da consulta. Sendo que esta consulta pode ser da forma que você quiser, com WHERE, ORDER BY, ou o que desejar.

 

Acho que é isso aí, havendo dúvidas fico a disposição, espero que seja últi, abraço.

[Marisa Lima 0]

Fiz a trigger da seguinte forma com o cursor, mas apenas me insere os dois primeiros registros e tem de inserir 100 e tal:

CREATE TRIGGER insert_momento_avaliacao

AFTER INSERT

ON momento_avaliacao

FOR EACH ROW

 

BEGIN

DECLARE varIdcolaborador int;

DECLARE id_momento int;

DECLARE f int DEFAULT 0;

DECLARE cursorExemplo CURSOR FOR SELECT id FROM colaborador WHERE activo='0';

DECLARE cursorExemplo2 CURSOR FOR SELECT id FROM momento_avaliacao WHERE id=new.id;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET f = 1;

 

OPEN cursorExemplo ;

OPEN cursorExemplo2;

REPEAT

FETCH cursorExemplo INTO varIdcolaborador ;

FETCH cursorExemplo2 INTO id_momento;

 

INSERT INTO momento_avaliacao_colaborador (id_colaborador,id_momento_avaliacao) VALUES (varIdcolaborador,id_momento);

UNTIL (f = 1) END REPEAT ;

 

CLOSE cursorExemplo ;

CLOSE cursorExemplo2 ;

END

Será que me podes dizer onde estou a errar?

[DiegoAngra07 21]

Bom dia,

 

Já executou os dois SELECTs dos cursores pra verificar quantas linhas estão sendo retornadas?

[Marisa Lima 0]

do primeiro select vai retornar 115 linhas neste momento, e o segundo só retorna uma.

O segundo o valor do segundo select vai ser sempre o mesmo o do primeiro é que altera.

Quando executo apenas insere dois registos faltando os outros 113.

[DiegoAngra07 21]

Então para o segundo tu não precisa de CURSOR, se tu tem certeza que SEMPRE será 1 valor.

 

Pros cursores múltiplos funcionarem o número de linhas retornadas precisa ser igual, pois senão o FETCH acabará primeiro em um.

 

Faça o segundo da maneira que você fez antes, numa variável fica. Aí vai dando FETCH no primeiro cursor e inserindo a mesma variável fixa pro segundo valor.

 

Deve funcionar.

 

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

 

Agora observando o seu código, notei que o teu segundo SELECT é assim:

 

SELECT id FROM momento_avaliacao WHERE id = new.id;

Se você está selecionando o ID que é igual ao NEW.id, não pode simplesmente usar o NEW.id ao invés de criar outra variável ?

[Marisa Lima 0]

Obrigada pela ajuda, já consegui colocar a funcionar. Ficou assim

Create Trigger insert_momento_avaliacao_colaborador

after insert

on momento_avaliacao

BEGIN

DECLARE varIdcolaborador int;

 

DECLARE f int DEFAULT 0;

DECLARE cursorExemplo CURSOR FOR SELECT id FROM colaborador WHERE activo='0';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET f = 1;

 

OPEN cursorExemplo ;

REPEAT

FETCH cursorExemplo INTO varIdcolaborador ;

 

INSERT INTO momento_avaliacao_colaborador (id_colaborador,id_momento_avaliacao) VALUES (varIdcolaborador,NEW.id);

UNTIL (f = 1) END REPEAT ;

 

CLOSE cursorExemplo ;

END

[DiegoAngra07 21]

Satisfação em ajudar, precisando estamos aí. Até a próxima =]

[Marisa Lima 0]

Precisei mais cedo do que estava á espera!

Preciso criar uma outra trigger para me inserir dados em uma tabela. O meu problema é o seguinte no meu modelo de dados tenho várias tabelas e pretendo que ao inserir na tabela momento_avaliacao_colaborador me insira dois campos dessa tabela numa outra, mais uns quantos dados vindos de uma 3º tabela, só que os dados da 3º tabela tem de estar de acordo com os id_colaborador da momento_avaliacao_colaborador.

Está um pouco dificil explicar!

 

Tabelas necessárias:

 

momento_avaliacao_colaborador onde vou buscar o id acabado de inserir, mas que por sua vez tem um colaborador associado;

 

competencia_funcional- onde vou buscar as competencias a inserir que por sua vez tambem tem um colaborador associado.

 

o meu problema é que ao inserir na tabela avaliacao_competencia_funcional_momento faça a correspondencia entre o colaborador da tabela momento_avaliacao_colaborador e a tabela competencia_funcional.

[DiegoAngra07 21]

Ok... Pelo que eu entendi:

 

Tabela A

Tabela B

Tabela C

 

Ao inserir um novo registro na Tabela A, será inserido um novo na Tabela B, com 2 dados do novo registro na A, e mais N dados vindos da Tabela C, que está relacionada com uma dessas duas (não identifiquei qual delas).

 

Com qual das duas tabelas a C se relaciona? Imagino que seja com a A, certo?

[Marisa Lima 0]

A tabela C relaciona-se com a A, existindo uma tabela chamada colaborador que relaciona C com B.

[DiegoAngra07 21]

Então são 4 tabelas?

 

A relaciona direto com C

B relaciona com D que relaciona com C

 

Isso?

 

Acho melhor postar a modelagem dessas tabelas aqui, ou os códigos para criá-las (CREATE TABLE de cada uma)

[Marisa Lima 0]

No Post anterior enganei-me!

tabela A relaciona com B

Tabela B relaciona com C

tabela C relaciona com D.

Seguem os create:

 

TABELA A:

CREATE TABLE `momento_avaliacao_colaborador` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`id_momento_avaliacao` INT(11) NULL DEFAULT NULL,

`id_colaborador` INT(11) NULL DEFAULT NULL,

`id_nivel_avaliacao_global` INT(11) NULL DEFAULT NULL,

`status` INT(11) NULL DEFAULT NULL,

PRIMARY KEY (`id`),

INDEX `FK_momento_avaliacao_colaborador_momento_avaliacao` (`id_momento_avaliacao`),

INDEX `FK_momento_avaliacao_colaborador_colaborador` (`id_colaborador`),

INDEX `FK_momento_avaliacao_colaborador_nivel` (`id_nivel_avaliacao_global`),

CONSTRAINT `FK_momento_avaliacao_colaborador_colaborador` FOREIGN KEY (`id_colaborador`) REFERENCES `colaborador` (`id`),

CONSTRAINT `FK_momento_avaliacao_colaborador_momento_avaliacao` FOREIGN KEY (`id_momento_avaliacao`) REFERENCES `momento_avaliacao` (`id`),

CONSTRAINT `FK_momento_avaliacao_colaborador_nivel` FOREIGN KEY (`id_nivel_avaliacao_global`) REFERENCES `nivel` (`id`)

)

COLLATE='utf8_unicode_ci'

ENGINE=InnoDB

AUTO_INCREMENT=1914;

 

TABELA B:

CREATE TABLE `avaliacao_competencia_funcional_momento` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`id_auto_avaliacao_nivel` INT(11) NULL DEFAULT NULL,

`justificacao_colaborador` LONGTEXT NULL COLLATE 'latin1_swedish_ci',

`id_avaliacao_chefe_nivel` INT(11) NULL DEFAULT NULL,

`justificacao_chefe` LONGTEXT NULL COLLATE 'latin1_swedish_ci',

`id_momento_avaliacao_colaborador` INT(11) NULL DEFAULT NULL,

`id_competencia_funcional` INT(11) NULL DEFAULT NULL,

`id_nivel_esperado` INT(10) NULL DEFAULT NULL,

PRIMARY KEY (`id`),

INDEX `FK_avaliacao_competencia_funcional_momento_competencia_funcional` (`id_competencia_funcional`),

INDEX `FK_avaliacao_competencia_funcional_momento_nivel` (`id_auto_avaliacao_nivel`),

INDEX `FK4` (`id_momento_avaliacao_colaborador`),

INDEX `FK_avaliacao_competencia_funcional_momento_nivel_2` (`id_avaliacao_chefe_nivel`),

INDEX `FK_avaliacao_competencia_funcional_momento_nivel_3` (`id_nivel_esperado`),

CONSTRAINT `FK4` FOREIGN KEY (`id_momento_avaliacao_colaborador`) REFERENCES `momento_avaliacao_colaborador` (`id`),

CONSTRAINT `FK_avaliacao_competencia_funcional_momento_competencia_funcional` FOREIGN KEY (`id_competencia_funcional`) REFERENCES `competencia_funcional` (`id`),

CONSTRAINT `FK_avaliacao_competencia_funcional_momento_nivel` FOREIGN KEY (`id_auto_avaliacao_nivel`) REFERENCES `nivel` (`id`),

CONSTRAINT `FK_avaliacao_competencia_funcional_momento_nivel_2` FOREIGN KEY (`id_avaliacao_chefe_nivel`) REFERENCES `nivel` (`id`),

CONSTRAINT `FK_avaliacao_competencia_funcional_momento_nivel_3` FOREIGN KEY (`id_nivel_esperado`) REFERENCES `nivel` (`id`)

)

COLLATE='utf8_unicode_ci'

ENGINE=InnoDB

AUTO_INCREMENT=943;

 

TABELA C:

CREATE TABLE `competencia_funcional` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`competencia` VARCHAR(500) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',

`indicador` VARCHAR(200) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',

`nivel_esperado` INT(11) NULL DEFAULT NULL,

`activo` INT(11) NULL DEFAULT NULL,

`id_colaborador` INT(11) NULL DEFAULT NULL,

`ano` INT(11) NULL DEFAULT NULL,

PRIMARY KEY (`id`),

INDEX `FK_competencia_funcional_colaborador` (`id_colaborador`),

INDEX `FK_competencia_funcional_nivel` (`nivel_esperado`),

CONSTRAINT `FK_competencia_funcional_colaborador` FOREIGN KEY (`id_colaborador`) REFERENCES `colaborador` (`id`),

CONSTRAINT `FK_competencia_funcional_nivel` FOREIGN KEY (`nivel_esperado`) REFERENCES `nivel` (`id`)

)

COLLATE='utf8_unicode_ci'

ENGINE=InnoDB

AUTO_INCREMENT=71;

 

TABELA D:

CREATE TABLE `colaborador` (

`id` INT(11) NOT NULL AUTO_INCREMENT,

`nome` VARCHAR(80) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',

`email` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',

`conta_windows` VARCHAR(255) NULL DEFAULT NULL COLLATE 'latin1_swedish_ci',

`activo` INT(11) NULL DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE INDEX `nome` (`nome`)

)

COLLATE='utf8_unicode_ci'

ENGINE=InnoDB

AUTO_INCREMENT=117;

 

Não anexei o modelo porque não encontrei nenhuma opção!

[DiegoAngra07 21]

Ok... Vamos por partes pra confirmar tudo.

 

Preciso criar uma outra trigger para me inserir dados em uma tabela. O meu problema é o seguinte no meu modelo de dados tenho várias tabelas e pretendo que ao inserir na tabela momento_avaliacao_colaborador me insira dois campos dessa tabela numa outra ...

A tabela onde a Trigger será criada é a momento_avaliacao_colaborador, que possui uma relação com a colaborador.

 

Pergunta 1 - Qual a tabela de destino da inserção (essa "numa outra" citada no quote acima) ? É a Tabela B chamada avaliacao_competencia_funcional_momento ?

Pergunta 2 - Quais os dados da tabela momento_avaliacao_colaborador você precisa inserir nessa tabela?

 

... mais uns quantos dados vindos de uma 3º tabela ...

Pergunta 3: Qual a terceira tabela? É a Tabela C? A competencia_funcional ?

Pergunta 4: Quais os dados dessa tabela que serão inseridos na tabela de destino?

 

Precisei mais cedo do que estava á espera!

... só que os dados da 3º tabela tem de estar de acordo com os id_colaborador da momento_avaliacao_colaborador ...

Pergunta 5: Ou seja, o id_colaborador que acabou de ser inserido será utilizado para uma busca na 3ª tabela pelos dados que deseja certo?

 

Se for como estou pensando não será muito complicado, só é mais difícil entender a situação primeiro. Por isso fiz as perguntas, pra confirmar tudo e depois partir pro código.

 

No aguardo, abraço.

[Marisa Lima 0]

A tabela onde a trigger será criada é a momento_avaliacao_colaborador, que possui uma relação com a tabela avaliacao_competencia_funcional_momento

 

Resposta Peg 1 - a tabela de destino de inserção é a tabela B chamada avaliacao_competencia_funcional_momento;

 

Resposta Peg 2 - os dados são apenas a chave primária (id) que é chave estrangeira na de inserção.

 

Respost Peg 3 - é a tabela competencia_funcional;

 

Resposta Peg 4 - sim, é isso mesmo.

 

Conforme ele insere na tabela avaliacao_competencia_funcional_momento tem de verificar se o id da tabela momento_avaliacao_colaborador o id_colaborador desse registo é o mesmo da tabela competencia_funcional.

Obriagada por todo o esforço em perceber o que pretendo!

[DiegoAngra07 21]

A tabela onde a trigger será criada é a momento_avaliacao_colaborador, que possui uma relação com a tabela avaliacao_competencia_funcional_momento

 

Resposta Peg 1 - a tabela de destino de inserção é a tabela B chamada avaliacao_competencia_funcional_momento;

 

Resposta Peg 2 - os dados são apenas a chave primária (id) que é chave estrangeira na de inserção.

 

Respost Peg 3 - é a tabela competencia_funcional;

 

Resposta Peg 4 - sim, é isso mesmo.

 

Conforme ele insere na tabela avaliacao_competencia_funcional_momento tem de verificar se o id da tabela momento_avaliacao_colaborador o id_colaborador desse registo é o mesmo da tabela competencia_funcional.

Obriagada por todo o esforço em perceber o que pretendo!

Nada, fico satisfeito em ajudar.

 

Acho que confundiu a pergunta 4:

 

Pergunta 4: Quais os dados dessa tabela que serão inseridos na tabela de destino?

 

Em relação ao que acabou de postar.

 

Como assim ele tem que verificar conforme é inserido?

A relação de chave estrangeira já não garante essa integridade?

Se por acaso o id que está sendo inserido não atender ao que você quer, a inserção não pode ser feita?

 

Se for este o caso precisará de uma Trigger de BEFORE INSERT.

 

Senão é só prosseguirmos com o raciocínio e partir pra criação da Trigger.

[Marisa Lima 0]

Da tabela competencia_funcional pretendo obter apenas o id da tabela.

Sim a chave estrangeira garante a integridade!

[DiegoAngra07 21]

Ok, seria isto?

 

DELIMITER $$

CREATE TRIGGER nomeTrigger 
AFTER INSERT ON momento_avaliacao_colaborador FOR EACH ROW
BEGIN
   DECLARE varIdCompetenciaFuncional INT;

   SET varIdCompetenciaFuncional = (SELECT cf.id FROM competencia_funcional cf WHERE cf.id_colaborador = NEW.id_colaborador);

   INSERT INTO avaliacao_competencia_funcional_momento (id_momento_avaliacao_colaborador, id_competencia_funcional) 
       VALUES (NEW.id, varIdCompetenciaFuncional);
END $$

Inserindo apenas os 2 IDs como você disse. Foi o que entendi :P

 

Tem que se atentar na linha do SET varIdCompetenciaFuncional, pois o SELECT nela deve retornar apenas 1 linha. O seu modelo garante que isso aconteça? Senão quem sabe precise de mais condições ali, ou um LIMIT 1.

 

Qualquer coisa posta aí que vamos vendo.