MySQL e trigger

[nataniel]

Pessoal,

Preciso de um ajuda daqueles que entendem de MySQL. Suponham a seguinte estrutura de dados:

tabela1
- id
- nome
- bloq

tabela2
- id
- nome

Quero criar um trigger entre as tabelas para que haja uma cópia de algumas linhas baseadas em um critério. Estou me batendo horrores para criar isso. A idéia é a seguinte:

Quando eu criar, apagar ou modificar uma nova linha na tabela1 que ela passe para a tabela2 entendo alguns critérios. São eles:

1) Caso INSERT na tabela 1: em caso de inserção de dados na tabela1 então quero que verifique se o campo bloq é N o conteúdo dele então quero que a linha seja copiada para a tabela2.

2) Caso REMOVE na tabela 1: em caso de remover um nome da tabela 1 então esta mesma linha deve ser removida (com o mesmo nome) da tabela 2.

3) Caso UPTATE na tabela 1: em caso de fazer uma alteração nas linhas da tabela 1 preciso que seja verificado se o campo BLOQ foi modificado para SIM. Se foi modificado para SIM entao a linha deve ser copiada a tabela 2. Se foi modificado para NAO entao a linha deve ser apagada da tabela 2. Em caso de alteracao do NOME a linha deve ser copiada apenas quando o campo BLOQ estiver em SIM.

Sei que é perfeitamente possível fazer isso usando TRIGGERS mas não estou conseguindo setar a sintaxe dele. Alguém pode me dar uma mão?

[marcelocbf]

Basicamente os triggers tem o formato de qualquer procedure ou funcao MySQL separados por um delimitador.

Voce pode criar um trigger antes ou depois das tres operacoes basicas, ou seja, INSERT, UPDATE ou DELETE, um trigger e um bloco de statements executados para cada linha modificada. Ou seja, para vc cria um trigger que vai executar antes de inserir um registro na tabela1 assim:

CREATE TRIGGER nome_do_trigger BEFORE INSERT ON tabela1
FOR EACH ROW
BEGIN
statement1; .....
statement2; .....
statement3; .....
etc ...
END;

Resumindo, vc acessa a linha modificada, a qual vc quer verificar com as palavras-chave OLD ou NEW, dependendo de qual tempo esta executando a acao, ou seja ...

Trigger no INSERT - So existe o NEW
Trigger no UPDATE - Existe o OLD e NEW
Trigger no DELETE - So existe o OLD

Para integridade dos dados, vale lembrar que vc pode escolher o BEFORE (antes) ou AFTER (depois) da acao. Se vc escolher um trigger antes do UPDATE qualquer erro que ocorrer o UPDATE nao vai ser executado, já se vc escolher depois o UPDATE ja foi executado e se der algum erro, o update, ou insert, etc. já terá sido realizado na tabela1.

Resumindo, vou fazer o seu primeiro exemplo ... (vou fazer com o AFTER, mas vc pode escolher o BEFORE dependendo da sua necessidade)

CREATE TRIGGER qualquer_nome AFTER INSERT ON tabela1
FOR EACH ROW
BEGIN
IF NEW.bloq = "N" THEN
INSERT INTO tabela2 (id, nome) VALUES (NEW.id, NEW.nome);
END IF;
END;

P.S: Lembre-se que quando vc cria um trigger vc tem que mudar o delimitador padrao, porque, vamos dizer no exemplo acima, se deixar o delimitador como ";" o MySQL para de executar o CREATE TRIGGER no INSERT ... Dependendo do programa que usa para executar comandos no MySQL, vc configura o delimitador usando a palavra DELIMITER

[nataniel]

Show de bola Marcelo. O IF no quadro me ajudou imensamente... Obrigado. Vou testar aqui e depois mando notícias.

[nataniel]

CREATE TRIGGER nome_do_trigger BEFORE INSERT ON tabela1
FOR EACH ROW
BEGIN
statement1; .....
statement2; .....
statement3; .....
etc ...
END;

Marcelo,

Com a sua ajuda eu consegui criar o trigger do modo como eu queria mas estou com um problema ainda. Vou postar o conteúdo como foi criado o triger e sinalizarei onde está o problema:

DELIMITER |
 
CREATE TRIGGER radius_upd AFTER UPDATE ON radacct_1[INDENT]FOR EACH ROW[/INDENT][INDENT]BEGIN[/INDENT][INDENT][INDENT]IF NEW.AcctStopTime IS NOT NULL THEN[/INDENT][/INDENT][INDENT][INDENT]INSERT INTO radacct (`RadAcctId`, `AcctSessionId`, `AcctUniqueId`, `UserName`, `GroupName`, `Realm`, `NASIPAddress`, `NASPortId`, `NASPortType`, `AcctStartTime`, `AcctStopTime`, `AcctSessionTime`, `AcctAuthentic`, `ConnectInfo_start`, `ConnectInfo_stop`, `AcctInputOctets`, `AcctOutputOctets`, `CalledStationId`, `CallingStationId`, `AcctTerminateCause`, `ServiceType`, `FramedProtocol`, `FramedIPAddress`, `AcctStartDelay`, `AcctStopDelay`, `xascendsessionsvrkey`) VALUES (NEW.RadAcctId, NEW.AcctSessionId, NEW.AcctUniqueId, NEW.UserName, NEW.GroupName, NEW.Realm, NEW.NASIPAddress, NEW.NASPortId, NEW.NASPortType, NEW.AcctStartTime, NEW.AcctStopTime, NEW.AcctSessionTime, NEW.AcctAuthentic, NEW.ConnectInfo_start, NEW.ConnectInfo_stop, NEW.AcctInputOctets, NEW.AcctOutputOctets, NEW.CalledStationId, NEW.CallingStationId, NEW.AcctTerminateCause, NEW.ServiceType, NEW.FramedProtocol, NEW.FramedIPAddress, NEW.AcctStartDelay, NEW.AcctStopDelay, NEW.xascendsessionsvrkey);[/INDENT][/INDENT][INDENT][INDENT][COLOR=Red][B]DELETE FROM radacct_1 WHERE OLD.RadAcctId = NEW.RadAcctId;[/B][/COLOR][/INDENT][/INDENT][INDENT][INDENT]END IF;[/INDENT][/INDENT][INDENT]END;[/INDENT]|
 
DELIMITER ;

A primeira linha que copia os dados da tabela que está recebendo o UPDATE está perfeita e copiando mas depois de copiados os dados quero que a tabela inicial suma com a linha que terá conteúdo na coluna AcctStopTime. Já tentei 1001 peripécias mas até o momento não consegui.

Se puderes me ajudar novamente, neste comando de DELETE eu agradeço imensamente.

[marcelocbf]

DELETE FROM radacct_1 WHERE OLD.RadAcctId = NEW.RadAcctId;

Eu ia perguntar se isso gerava algum erro na engine do MySQL, mas creio que não chega a gerar pois ele não deve encontrar nada pra deletar, e sinceramente acho que ele não vai te deixar executar uma outra operação no row com a operação atual não-"oficialmente" terminada, mas vc pode tentar para ver realmente se não ...
O que acontece com o statement acima é que quando vc diz "WHERE OLD.RadAcctId" vc está referenciando um valor e não a coluna RadAcctId, o certo seria vc pôr ... DELETE FROM radacct_1 WHERE RadAcctId = NEW.RadAcctId;

Poderia te dar uma sugestão que também tenho 99,9% de certeza que ele não vai te deixar executar que seria criar um trigger no INSERT na tabela radacct para DELETE FROM radacct_1 WHERE RadAcctId = NEW.RadAcctId AND AccStopTime IS NOT NULL, pois acho que apesar de estar "oficialmente" em operações diferentes a primeira row ainda vai estar locked, mas como a primeira operação é um UPDATE vc pode tentar ... Eu sou tipo Tomé, só acredito que não dá certo, quando vejo o erro, rs ...

Ou vc poderia criar um campo na tabela radacct_1 para sinalizar que aquele registro seria um candidato à deleção e à sua conveniência passar um script de deleção nesses registros ... mas acho que essa segunda opção não é tão elegante ...

Falow,

Feliz Ano Novo ...

[nataniel]

Vou colocar as respostas em partes.

Eu ia perguntar se isso gerava algum erro na engine do MySQL, mas creio que não chega a gerar pois ele não deve encontrar nada pra deletar, e sinceramente acho que ele não vai te deixar executar uma outra operação no row com a operação atual não-"oficialmente" terminada, mas vc pode tentar para ver realmente se não ...
O que acontece com o statement acima é que quando vc diz "WHERE OLD.RadAcctId" vc está referenciando um valor e não a coluna RadAcctId, o certo seria vc pôr ... DELETE FROM radacct_1 WHERE RadAcctId = NEW.RadAcctId;

Ele não gera erro e a sua indicação de DELETE é o que eu fiz primeiro que também não funcionou. Ele copia o registro mas não apaga ele da primeira tabela. Tentei 1001 opções usando o NEW e o OLD em ambos os lados. Tentei também um DELETE dos campos que não eram nulos naquela coluna mas também não surtiu resultado algum.

Poderia te dar uma sugestão que também tenho 99,9% de certeza que ele não vai te deixar executar que seria criar um trigger no INSERT na tabela radacct para DELETE FROM radacct_1 WHERE RadAcctId = NEW.RadAcctId AND AccStopTime IS NOT NULL, pois acho que apesar de estar "oficialmente" em operações diferentes a primeira row ainda vai estar locked, mas como a primeira operação é um UPDATE vc pode tentar ... Eu sou tipo Tomé, só acredito que não dá certo, quando vejo o erro, rs ...

O problema é que o INSERT quando é feito nessa tabela sempre terá o AcctStopTime como NULL. Este só será modificado quando houver um UPDATE. Obrigatóriamente em todos os UPDATE esse campo é alterado. Se em um novo INSERT na tabela eu pudesse remover os campos que tem o AcctStopTime não nulos seria interessante mas acho que a rotina só permite mexer na linha que está sendo inserida/corrigida.

Ou vc poderia criar um campo na tabela radacct_1 para sinalizar que aquele registro seria um candidato à deleção e à sua conveniência passar um script de deleção nesses registros ... mas acho que essa segunda opção não é tão elegante ...

Falow,

Feliz Ano Novo ...

Esta última realmente é impraticável já que a tabela é de um programa de autenticação de clientes (FreeRadius) e eu teria que recompilar o módulo MySQL dele para que ele coloque uma nova coluna quando há update na tabela radacct. Sinceramente é muito complexo por meu nível de programação... hahahahahaha...

Me ocorreu o seguinte. Como eu executo o TRIGGER logo que o UPDATE é feito eu poderia, tranquilamente, colocar um cron job no shell do meu box linux que execute um comando simples do tipo:

DELETE FROM radacct_1 WHERE AcctStopTime IS NOT NULL

E esse comando rode todos os dias, por exemplo, ou a cada hora... Acho que já ajudaria meu problema... Que acha?

[marcelocbf]

Ele não gera erro e a sua indicação de DELETE é o que eu fiz primeiro que também não funcionou. Ele copia o registro mas não apaga ele da primeira tabela. Tentei 1001 opções usando o NEW e o OLD em ambos os lados. Tentei também um DELETE dos campos que não eram nulos naquela coluna mas também não surtiu resultado algum.

Verifique o log do MySQL para ver se não aparece nenhum erro lá que possa esclarecer, pois foi como disse, o DELETE não pode ter o referenciador, pois vc não estará se referindo à coluna, mas talvez no log te mostre algum erro, pois creio que ele não deixe vc efetuar um comando que "modifique" a linha que já está sendo operada no momento ...

O problema é que o INSERT quando é feito nessa tabela sempre terá o AcctStopTime como NULL. Este só será modificado quando houver um UPDATE. Obrigatóriamente em todos os UPDATE esse campo é alterado. Se em um novo INSERT na tabela eu pudesse remover os campos que tem o AcctStopTime não nulos seria interessante mas acho que a rotina só permite mexer na linha que está sendo inserida/corrigida.

Não, estava me referindo à um trigger no insert do radacct, que deletaria o radacct_1, pois o seu primeiro trigger e no update de radacct_1, irá inserir um registro em radacct. Então vc poderia criar um trigger no insert do radacct para deletar de radacct_1 os registros que não forem nulos ... Ficaria a mesma coisa do seu trigger atual, mas em dois triggers diferentes ... mas como também disse ... acho que o MySQL não vai te deixar fazer isso por integridade dos dados ....

Esta última realmente é impraticável já que a tabela é de um programa de autenticação de clientes (FreeRadius) e eu teria que recompilar o módulo MySQL dele para que ele coloque uma nova coluna quando há update na tabela radacct. Sinceramente é muito complexo por meu nível de programação... hahahahahaha...

Na verdade, não precisa fazer nada extraordinário, e eu estava sugerindo criar um campo na tabela radacct_1 que não é usada pelo FreeRadius (a não ser se vc fez uma instalação custom), e mesmo assim não precisaria recompilar, pois as queries executadas pelo FreeRadius ficam dentro da pasta sql/mysql tera um conf que dependendo do seu conf principal é o responsável pelos queries e eles estão lá, podendo ser modificados normalmente ... Mas também, depois que dei a sugestão, vi que realmente não precisa de outro campo identificador, só realmente apagar o que AcctStopTime não for nulo ...

Me ocorreu o seguinte. Como eu executo o TRIGGER logo que o UPDATE é feito eu poderia, tranquilamente, colocar um cron job no shell do meu box linux que execute um comando simples do tipo:

DELETE FROM radacct_1 WHERE AcctStopTime IS NOT NULL

E esse comando rode todos os dias, por exemplo, ou a cada hora... Acho que já ajudaria meu problema... Que acha?

Na verdade, quando sugeri o script que à sua conveniência seria rodado estava pensando no cron mesmo, via mysql command line ou outra linguagem de programacao preferida ...

Falow

[nataniel]

Marcelo,

Obrigado pelas informações. Já consegui resolver os problemas... Desculpe ter tomado seu tempo e, se precisar de alguma coisa, me avisa...

[marcelocbf]

Ah beleza,

Vc conseguiu com o trigger mesmo, ou outra maneira ? Falow ...

[nataniel]

Vc conseguiu com o trigger mesmo, ou outra maneira ? Falow ...

Com o TRIGGER eu copiei e fiz um shell script simples que roda todos os dias e apaga os dados da tabela radacct_1 onde o AcctStopTime não é nulo. Algo simples, prático e rápido.