Trigger Em postgreeSQL
22/09/2012
0
Tenho duas tabelas:
================================================
EDITORAS
codeditora | cnpj | razaosocial(nome da editora) | cidade | qtdlivros
================================================
================================================
LIVROS
isbn(é o codigo do livro) | titulo | valor | codeditora
================================================
Preciso criar uma TRIGGER de modo que sempre que eu adicionar um livro na tabela LIVROS
a tabela EDITORA tenha a coluna QTDLIVROS atualizada com a quantidade de livros da
respectiva editora. tanto para mais quanto para menos no caso de exclusão de um livro.
Estou a dois dias pesquisando até de madrugada e não consigo chegar a uma solução, sei que é
simples, mas não tenho conhecimento profundo na sintaxe do postgresql.
Alguém pode me ajudar?
Desde já muito obrigado.
Luiz Guilherme
Posts
22/09/2012
Bruno Leandro
CREATE OR REPLACE FUNCTION fn_trigger_LIVROS() RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
update EDITORAS set qtdlivros = qtdlivros - 1 where codeditora= old.codeditora;
ELSIF (TG_OP = 'INSERT') THEN
update EDITORAS set qtdlivros = qtdlivros + 1 where codeditora= new.codeditora;
END IF;
IF (TG_OP = 'DELETE') THEN
RETURN OLD;
ELSIF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;
CREATE TRIGGER trigger_LIVROS AFTER INSERT OR UPDATE OR DELETE ON LIVROS FOR EACH ROW EXECUTE PROCEDURE fn_trigger_LIVROS();
22/09/2012
Luiz Guilherme
CREATE OR REPLACE FUNCTION fn_trigger_LIVROS() RETURNS trigger AS
$BODY$
BEGIN
IF (TG_OP = 'DELETE') THEN
update EDITORAS set qtdlivros = qtdlivros - 1 where codeditora= old.codeditora;
ELSIF (TG_OP = 'INSERT') THEN
update EDITORAS set qtdlivros = qtdlivros + 1 where codeditora= new.codeditora;
END IF;
IF (TG_OP = 'DELETE') THEN
RETURN OLD;
ELSIF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
RETURN NEW;
END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;
CREATE TRIGGER trigger_LIVROS AFTER INSERT OR UPDATE OR DELETE ON LIVROS FOR EACH ROW EXECUTE PROCEDURE fn_trigger_LIVROS();
Olá Bruno.
Cara, funcionou perfeito!!!
Agora eu queria, humildemente te pedir uma explicação sobre os códigos utilizados
para que eu entenda como ele funciona ok.
por exemplo,
a) "(TG_OP = 'DELETE')" aparentemente ele diz que quando alguma linha for apagada a tabela editora,
na coluna qtdlivros sera atualizada decrementando 1, é isto?
Depois tem o "where codeditora = old.codeditora;" que não entendi o que ele está fazendo,
qual a função do "old." ?
b) "(TG_OP = 'INSERT')" aqui parece sere o contrário, quando alguma linha é adicionada, a coluna qtdlivros
é incrementada em 1. mas apareceu de novo o comando "where codeditora= new.codeditora;" agora com new.
qual a diferença para o old.?
c) " IF (TG_OP = 'DELETE') THEN
RETURN OLD;
ELSIF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
RETURN NEW;
END IF; "
POR ÚLTIMO, qual a função desta parte do código, se o meu raciocinio estiver correto nas anteriores,
a grosso modo quer dizer que se eu deletar uma linha o retorno sera OLD. e se a linha for inserida
ou atualizada será NEW (o que isto quer dizer? e como isso influencia no resultado?)
Peço desculpas se estou abusando de seu conhecimento,
é que eu quero aprender também, assim posso aplicar em outras situações.
Você conhece algum material livre onde eu encontre uma explicação sobre estes comandos?
Muito obrigado.
Delai.
22/09/2012
Bruno Leandro
quando tiver inserindo o new é o valor que esta inserindo e o old tudo null, no update o old é o registro antes da atualização e o new são os novos valores, e no delete o old contem os valores antes da exclusão e o new é tudo null.
tem esse site que possui uma explicação bem bacana sobre o assuntos http://tas.eti.br/blog/2011/12/manipulando-triggers-no-postgresql/
livros de postgre na sua maioria nao são bons, eu indico que estudo a documentação oficial hoje na minha opinião é a melhor opção
com relação ao item c poderia retornar return null; pois é executado alter ou seja depois do insert, update e delete entao nao ira afetar, mas eu utilizo por padrão no começo deste site tem uma explicação sobre isso
http://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Fun%C3%A7%C3%B5es_Definidas_pelo_Usu%C3%A1rio_e_Triggers/Triggers
24/09/2012
Luiz Guilherme
Olá.
Obrigado pelo retorno, você tem sido muito paciente e interessado em me ajudar.
dei uma lida no material que você indicou, muito bom, após tentei fazer este procedimento
abaixo e mesmo assim não consegui.
faço um curso federal a distancia, o material é uma droga, e nossa professora não está postando
um material complementas que ensine o que esta sendo cobrado, já entrei em contato com a
coordenação, porém enquanto não obtenho um retorno, os prazos para entrega das tarefas
estão expirando e não está dando tempo de pesquisar sobre o conteúdo, resolver e postar
em tempo hábil.
como você vê, não é nenhum bixo de sete cabeças, porém não estão disponibilizando base
para que a gente aprenda antes de passar os exercícios.
por isto gostaria de contar com sua ajuda na solução destes problemas para que eu não
perca pontos importantes até eu conseguir acompanhar o conteúdo cobrado.
com certeza também será um conteúdo que irá enriquecer a base do fórum, pois outros
poderão ter as mesmas dúvidas no futuro.
segue abaixo a tarefa que expira amanhã.
Tenho as tabelas:
======================================================================================
ALUNOSTURMA
cod_alunosturma | fk_cod_aluno | fk_cod_turma | situacao
======================================================================================
TURMA
cod_turma | fk_cod_curso | ano | semestre | vagas_total | vagas_preenchidas
======================================================================================
CURSO
cod_curso | nome_curso | carga_horaria
======================================================================================
Eu quero fazer o seguinte:
a) Criar uma função que atualize a coluna 'vagas_preenchidas' da tabela 'TURMA'.
Esta função deve contabilizar quantos alunos estão matriculados (situacao = 'matriculado')
de acordo com a tabela de 'ALUNOSTURMA'.
- (CREIO QUE DEVA SOMAR A QUANTIDADE DE LINHAS QUE CONSTEM A PALAVRA MATRICULADO, E
COLOCAR ESTE NÚMERO DENTRO DA COLUNA 'vagas_preenchidas' mas não estou conseguindo
implementar isto)
b) Fazer um trigger que haja da seguinte forma: quando uma linha da tabela de 'ALUNOSTURMA'
for excluída, a quantidade de vagas preenchidas (coluna 'vagas_preenchidas') da tabela
'TURMA' é subtraído de 1 (um).
c) E outro trigger que toda vez que a coluna 'situacao' da tabela de 'ALUNOSTURMA' for
alterado para 'cancelado', a quantidade de vagas preenchidas (coluna 'vagas_preenchidas')
da tabela 'TURMA' também seja subtraído de 1 (um).
- (PELA FORMA QUE as letras B) e C) DO ENUNCIADO FORAM ESCRITAS, ME PARECE QUE O PROPRIO
GATILHO VAI VERIFICAR AS ALTERAÇÕES NA TABELA 'ALUNOSTURMA' E EM SEGUIDA FAZER AS ALTERAÇÕES
NA COLUNA 'vagas_preenchidas' pois aqui não fala nada de função, isto é possível? como faço?)
d) Fazer uma função que recebe o código de um curso e exibe os dados de todas as turmas
do código do curso passado que possuem vagas, isto é, cujo valor de vagas preenchidas é
menor que a quantidade de vagas total.
- (NÃO ENTENDI ESTA LETRA D, Como vou passar este código para a função? )
Desde já agradeço sua solidariedade.
Luiz Guilherme.
25/09/2012
Luiz Guilherme
Eu consegui até certo ponto.
desenvolvi o código abaixo, nele a quantidade de alunos é atualizada sempre que modifico alguém para matriculado.
porém, a contagem não é feita por turma, se tiver 3 alunos matriculados um em cada turma diferente, cada uma delas
está recebendo a soma dos tres, queria que cada turma recebesse a contagem só dos alunos matriculados nela, veja se
pode me ajudar?.
obrigado.
================================================================
CREATE OR REPLACE FUNCTION fn_vagaspre() RETURNS trigger AS
$BODY$
DECLARE vagaspre INT = COUNT(*)
FROM alunosturma
WHERE situacao = 'matriculado';
BEGIN
UPDATE Turma SET vagas_preenchidas = vagaspre
FROM alunosturma, curso
WHERE turma.cod_turma = alunosturma.fk_cod_turma
AND turma.fk_cod_curso = curso.cod_curso;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE COST 100;
CREATE TRIGGER tr_vagaspre
AFTER INSERT OR UPDATE
ON alunosturma
FOR EACH ROW
EXECUTE PROCEDURE fn_vagaspre();
============================================================
25/09/2012
Luiz Guilherme
=========================================================================
CREATE OR REPLACE FUNCTION fn_vagaspre()
RETURNS trigger AS
$BODY$
DECLARE count_turmas INT := 0; -- indice contador de turmas
DECLARE total_turmas INT := MAX(cod_turma) FROM turma; -- Maior código das turmas (define até onde o loop deve agir)
DECLARE soma_vagas_preenchidas INT := 0; -- total de vagas preenchidas por turma
BEGIN
WHILE count_turmas < total_turmas -- Enquanto o contador não atingir o total de turmas (código mais alto)
LOOP
count_turmas = count_turmas + 1; -- contador define qual turma sera atualizada...
soma_vagas_preenchidas = COUNT(*) -- soma todas as linhas...
FROM alunosturma -- da tabela alunos turma...
WHERE situacao = 'matriculado' -- que conhenham texto matriculado...
AND fk_cod_turma = count_turmas; -- e contenham o codigo da turma x (x é o código atualmente armazenado em count_turmas)...
UPDATE Turma SET vagas_preenchidas = soma_vagas_preenchidas -- atualiza a quantidade de vagas preenchidas da tabela turma...
WHERE cod_turma = count_turmas; -- somente na linha que contem a turma x
END LOOP;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fn_vagaspre()
OWNER TO postgres;
================================================================================================
CREATE TRIGGER tr_vagaspre
AFTER INSERT OR UPDATE OR DELETE
ON alunosturma
FOR EACH ROW
EXECUTE PROCEDURE fn_vagaspre();
================================================================================================
agora só falta a ultima parte que eu nao compreendi direito.
Clique aqui para fazer login e interagir na Comunidade :)