PostgreSQL: Triggers, transações, erros e cursores

Triggers - Sobre o curso

Sejam muito bem-vindos à Alura. Meu nome é Vinicius Dias, embora vocês não estejam me vendo eu vou guiar vocês por mais um treinamento de programação utilizando PostgreSQL.

Neste treinamento vamos ver algumas coisas bem interessantes. Como por exemplo vamos resolver aquele problema de que alguém poderia simplesmente inserir um instrutor se executar a função e não teríamos os logs.

Vamos resolver isso e além de resolver vamos complementar nossa função com muitas coisas interessantes, com algumas verificações a mais, e com isso nesse processo vamos aprender algumas pegadinhas sobre gerenciamento de transação dentro de funções, utilizando plpgsql, vamos ver como gerenciar, como tratar e como gerar erros, e como isso pode ser útil para nós.

também vamos falar um pouco sobre como o Postgres trabalha por baixo dos pano em alguns casos, utilizando cursores. Vamos ver como criar na mão algum cursor, mas vamos entender que o PostgreSQL já faz em diversos casos esse trabalho de forma automática para nós.

vamos aprender sobre blocos anônimos, vamos conversar um pouco sobre boas práticas, vamos conversar sobre algumas ferramentas de mercado. Vai ser um bate-papo legal, espero que você aproveite. E durante o treinamento, caso você fique com alguma dúvida, não hesite. Você pode abrir uma dúvida lá no fórum.

Eu tento ajudar pessoalmente sempre que possível, mas quando não consigo nossa comunidade de alunos, moderadores e instrutores é muito solícita e alguém com certeza vai conseguir te ajudar.

Espero que você tire proveito deste treinamento, espero que você aprenda bastante, e te vejo no próximo vídeo para começarmos a conversar sobre o que vamos fazer, começar a brincar um pouco.

Triggers - Eventos no banco

Sejam bem-vindos de volta. Nesse treinamento vamos dar continuidade no que trabalhamos, no último treinamento, sobre programação utilizando o Postgres. Qual o problema que encontramos? Qual situação nos deparamos no final do último treinamento?

Temos uma função muito interessante que cria um instrutor, e baseado na criação desse instrutor, registra alguns logs, informando se esse instrutor recebe ou não acima da média, informando ainda quantos instrutores recebem menos.

Fiz uma pequena modificação aqui, que disse que esse tipo decimal só vai ter cinco dígitos, sendo dois deles depois da casa decimal. Dessa forma temos uma representação um pouco mais amigável.

Mas continuando, ele salva dois logs, então armazenei alguns logs para vocês darem uma olhada em como temos isso desenvolvido. Esse ponto já fizemos no último treinamento, caso você não esteja entendendo o que está acontecendo aqui é porque você ainda não fez o último treinamento, então corre lá.

Mas continuando, nosso problema é que posso muito bem fazer diretamente um insert into instrutor, passar os campos, os valores necessários para instrutor, passar o nome e salário, e isso não vai armazenar meu log.

Isso vai fazer com que eu tenha minha tabela de logs desatualizada. Isso vai fazer com que eu não tenha todas as informações necessárias, e isso vai fazer com que eu possa arbitrariamente inserir instrutores que recebem muito mais do que a média sem que eu tenha nenhuma informação sobre isso de forma mais direta, sendo que preciso de forma manual sempre ficar monitorando a tabela de instrutores, sendo que já tenho a tabela de instrutores, que me traz essas informações com mais detalhes.

O que eu quero é que quando, e absorva essa palavra, quando eu inserir um instrutor, alguma coisa precisa acontecer. E se tenho a palavra quando, normalmente, pensando principalmente em programação, caso você já esteja familiarizado com programação, mas mesmo que não esteja, na vida real mesmo, quando temos a palavra quando remetemos ao conceito de eventos, pensamos logo em um evento que pode acontecer.

No evento de inserção de um instrutor quero que algo aconteça. Pode ser logo antes de inserir esse instrutor, pode ser logo depois de inserir esse instrutor, e vamos trabalhar em cima dessa ideia de eventos.

Eventos no banco de dados possuem um nome um pouco diferente, vamos dizer assim, mas é algo bastante comum, que são os triggers. Ou gatilhos, trazendo para o português.

Os triggers no bancos de dados são formas de executar um código sempre que algum evento acontecer. Então, por exemplo, caso eu tenha após inserir algo, eu vou realizar alguma função. É esse o tipo de funcionalidade, essa é a funcionalidade que precisamos para atingir nosso objetivo.

Precisamos criar triggers, ou precisamos criar gatilhos para que quando um instrutor for inserido, por exemplo, logo depois que eu inseri um instrutor, eu executar todo esse código. Todo esse pedaço de código.

O que queremos que aconteça? Queremos que esse insert seja executado normalmente pelo usuário, pela pessoa que está utilizando o banco de dados. A pessoa vai executar o insert e automaticamente, por baixo dos panos, o banco de dados, o PostgreSQL vai falar “opa, um instrutor foi inserido, então preciso executar tudo isso de forma automática, porque sei que preciso, fui configurado para isso”.

Essa configuração, essa ideia de criar um evento, de criar um gatilho para executar quando um evento acontecer é o que vamos trabalhar nesse capítulo. No próximo vídeo paramos de falação e finalmente partimos para a prática.

Triggers - Trigger Procedures

Bem-vindos de volta. Vamos editar essa funcionalidade para que possamos utilizar triggers. No PostgreSQL como fazemos para criar um trigger? Precisamos definir o que é conhecido como trigger procedure, uma função que vai gerar um trigger. E se ela vai gerar um trigger, se ela vai gerar um gatilho, precisamos dizer que ela retorna um gatilho, certo? Então ela não mais vai retornar nada. O retorno dessa função vai ser um gatilho.

Continuando, uma coisa muito importante é que sempre que temos uma função que vai ser executada através de um trigger, não vamos receber nenhum parâmetro, não podemos receber nenhum parâmetro.

“Mas Vinicius, então como vou saber o nome do instrutor, o salário do instrutor que está sendo inserido?”, e aí que está uma coisa muito interessante. Em funções que são executadas por trigger, temos algumas variáveis especiais, definidas automaticamente pelo Postgres.

Vamos dar uma olhada na documentação para que você possa ver algumas dessas variáveis. Temos a variável new, que é do tipo record, ou seja, é aquele tipo genérico, pode vir qualquer coisa nessa variável. E ela vai ter a nova linha quando estivermos realizando um insert com update, e se for um delete que estamos tratando, essa new não vai ter nada.

Se eu estou executando um insert e um trigger é ativado, um gatilho é executado, então essa variável new vai ter exatamente a linha que está seno inserida. É isso que preciso. Através da variável new, consigo acessar o nome, e consigo acessar o salário do instrutor que está sendo inserido.

Logo, não preciso mais desses parâmetros mesmo. Show de bola, não temos problema nenhum quanto a isso. Continuando, esse insert obviamente vai ter que sair, porque não vamos mais executar o insert aqui. Quando o insert for executado, essa função vai ser chamada, ou seja, o instrutor já foi inserido.

Vou remover isso e colar lá embaixo para não me esquecer, e vamos continuar. Sendo assim também não preciso do id do instrutor inserido, porque tenho lá naquela variável new. Vou tirar isso e vamos modificar nossa função para que ela utilize aquele new.

Estamos pegando a média salarial da tabela instrutor onde o id não é igual a NEW.id, ou seja, não é igual ao id que acabou de ser inserido. Moleza até aqui. Estamos verificando se o salário do instrutor recém-inserido, então NEW.salario, é maior do que a média.

Até aqui só facilidade. Vou inserir NEW.nome, ou seja, o nome do instrutor recém-inserido. Até aqui tem sido bem tranquilo, vamos continuar. Para cada salário dos meus instrutores, da tabela de instrutor, de novo, diferente do id recém-inserido.

No if são todas as variáveis, NEW.salario, se o salário que estou inserindo agora for maior do que o salário percorrido desse select vou adicionar no contador e show de bola. Depois NEW.nome, que pega o percentual, e a princípio temos nossa função completa.

Vou fazer um drop dessa função porque lembra que se alteramos os parâmetros ou se alteramos o tipo de retorno não posso substituir, então DROP FUNCTION cria_instrutor. Removi essa função, agora vou recriar como uma trigger procedure, e vamos ver se vai dar tudo certo.

A princípio está tudo ok, então já temos uma função que gera um trigger, só que ainda não tenho o trigger criado, ainda não tenho efetivamente a ação configurada para ser executada depois do insert.

“Mas como não, Vinicius? A função está criada”. Sim, a função está criada, mas em que momento aqui estou dizendo que essa função vai ser executada depois de um insert na tabela instrutor? Ainda não configurei isso. Então antes de configurarmos, vamos dar uma batida de olho, uma passada de olho bem rápida sobre trigger procedures, ou seja, sobre funções que são executadas com triggers, para vermos algumas particularidades.

Temos diversas variáveis especiais, temos o new, que é o que utilizamos, ou seja, a nova linha que está sendo inserida, ou a nova linha depois de ser atualizada. Temos o old, que é a linha que está sendo removida, ou a linha antes da alteração, antes do update ser executado. E temos várias variáveis especiais. Vai ser o nome do trigger que está sendo executado, quando esse trigger está sendo executado, se é antes, se é depois, se é ao invés de alguma instrução.

O nível, e isso podemos tratar com um pouco mais de detalhes depois. Mas basicamente se é para cada linha ou para cada instrução executada. Dos conversamos sobre isso. Qual operação está sendo executada, se é um insert, um update, um delete, um truncate.

O id da tabela que foi modificada para gerar esse trigger, o nome da tabela que foi modificada. Tem muita coisa aqui. Você pode passar o olho depois e ver todas as variáveis com detalhes. Mas uma coisa interessante que quero mostrar aqui é que caso eu queira, por exemplo, baseado em uma condição cancelar a inserção de um instrutor, se esse instrutor estivesse acima da média, quero cancelar ele.

Essa função precisaria retornar, por exemplo, está acima da média salarial, vou retornar nulo, RETURN NULL. O que isso faria? Quando retorno nulo, sou permitido retornar nulo, primeiro é importante deixar isso claro, sou permitido retornar nulo, e caso eu retorne nulo, em uma função que é executada antes do insert, por exemplo, então se eu tivesse um trigger before insert, antes de inserir instrutor, o que vai acontecer?

Quando eu retornar nulo o trigger vai dizer para o Postgres “eu estou retornando nulo, isso quer dizer que a instrução não vai ser executada”. Então quando tenho um trigger que vai ser executado antes de uma funcionalidade, antes de uma instrução, eu cancelo a instrução quando retorno nulo.

Agora, caso eu queira que a instrução seja executada, posso a princípio não retornar nada, ou o que é bastante comum, posso retornar essa nova linha, esse novo registro. Essa é a palavra que eu estava buscando. Posso retornar esse novo registro informando que está tudo ok, que tudo deu certo, e isso é bastante comum.

Mas no nosso caso, para não confundir, vou deixar sem nada. De novo, esses detalhes, essas particularidades podemos bater um papo com mais detalhes, com mais especificidade, com mais profundidade em um futuro vídeo. Só queria dar uma passada bem rápida depois de criarmos a função nas variáveis especiais que temos de bandeja, que o Postgres já cria para nós. E também nesse detalhe de que posso cancelar uma execução se meu trigger for criado para antes da instrução.

Mas estou falando dessa parte de criar trigger e por enquanto só tenho a função. No próximo vídeo, porque esse já está longo, vamos finalmente criar esse trigger.

Sobre o curso PostgreSQL: Triggers, transações, erros e cursores

O curso PostgreSQL: Triggers, transações, erros e cursores possui 116 minutos de vídeos, em um total de 52 atividades. Gostou? Conheça nossos outros cursos de SQL e Banco de Dados em Data Science, ou leia nossos artigos de Data Science.

Matricule-se e comece a estudar com a gente hoje! Conheça outros tópicos abordados durante o curso:

• Triggers
• Gerenciamento de transações
• Erros e exceções
• Cursores
• Processo de desenvolvimento