{"id":5312,"date":"2023-04-25T10:10:00","date_gmt":"2023-04-25T07:10:00","guid":{"rendered":"http:\/\/professor.norton.net.br\/?p=5312"},"modified":"2023-04-26T01:16:01","modified_gmt":"2023-04-25T22:16:01","slug":"lbd-triggers-e-views","status":"publish","type":"post","link":"https:\/\/professor.norton.net.br\/index.php\/2023\/04\/25\/lbd-triggers-e-views\/","title":{"rendered":"LBD &#8211; Triggers e Views"},"content":{"rendered":"\n<div class=\"wp-block-file\"><a id=\"wp-block-file--media-b8f2b0bb-b7fd-4320-9e94-15fec8c17790\" href=\"https:\/\/professor.norton.net.br\/wp-content\/uploads\/2021\/10\/lbd07.pptx\">Apresenta\u00e7\u00e3o<\/a><a href=\"https:\/\/professor.norton.net.br\/wp-content\/uploads\/2021\/10\/lbd07.pptx\" class=\"wp-block-file__button wp-element-button\" download aria-describedby=\"wp-block-file--media-b8f2b0bb-b7fd-4320-9e94-15fec8c17790\">Baixar<\/a><\/div>\n\n\n\n<h3>Exemplo<\/h3>\n<pre>\nuse escola;\ncreate index idx_aluno_1 on aluno(nome);\n\ncreate index idx_aluno_2 on aluno(ra);\n\nalter table aluno drop index idx_aluno_1;\n\nuse pizzaria;\n\ncreate view malaDireta as \nselect nome, email, telefone from professor\nunion\nselect nome, email, telefone from aluno\norder by nome\n\nselect * from malaDireta\n\n\n\nselect * from pedido;\nselect * from item;\n\nDELIMITER  $$\ncreate trigger tri_item after insert on item \nfor each row\nbegin\t\n\tdeclare varTotal decimal(18,2);        \n    select sum(valorTotal) from item \n\t\twhere codigoPedido = NEW.codigoPedido\n        into varTotal;\n    update pedido set valorItens=varTotal, \n\t\tvalorTotal=varTotal + valorEntrega where\n        codigo = NEW.codigoPedido;\nend$$\nDELIMITER  ;\n\nselect * from item;\n\ninsert into item(codigoPedido, codigoProduto, qtd,\nvalorUnitario, valorTotal) values(1,4,1,12,12);\n\nselect * from pedido;\n<\/pre>\n\n\n\n<p><strong>Atividade<\/strong><\/p>\n\n\n\n<p>Utilizando o banco de dados da biblioteca fa\u00e7a:<\/p>\n\n\n\n<p>1-crie uma view chamada vw_emprestimos que fa\u00e7a uma consulta das tabelas leitor e empr\u00e9stimo trazendo o nome do cliente e&nbsp; nome da obra&nbsp;<\/p>\n\n\n\n<p>2-crie uma tabela chamada auditlog{codigo, nomeTabela, registroRemovido, data}&nbsp;<\/p>\n\n\n\n<p>3-crie uma trigger para exclus\u00e3o na tabela reserva que guarde na tabela auditlog os dados do registro removido<\/p>\n\n\n\n<p>4-crie uma trigger para exclus\u00e3o na tabela emprestimo que guarde na tabela auditlog os dados do registro removido<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Exemplo use escola; create index idx_aluno_1 on aluno(nome); create index idx_aluno_2 on aluno(ra); alter table aluno drop index idx_aluno_1; use pizzaria; create view malaDireta as select nome, email, telefone from professor union select nome, email, telefone from aluno order by nome select * from malaDireta select * from pedido; select * from item; DELIMITER $$ <span class=\"readmore\"><a class=\"more-link\" href=\"https:\/\/professor.norton.net.br\/index.php\/2023\/04\/25\/lbd-triggers-e-views\/\">Read More &#8230;<\/a><\/span><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[7],"tags":[],"class_list":["post-5312","post","type-post","status-publish","format-standard","hentry","category-laboratorio-de-banco-de-dados"],"_links":{"self":[{"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/posts\/5312","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/comments?post=5312"}],"version-history":[{"count":5,"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/posts\/5312\/revisions"}],"predecessor-version":[{"id":5661,"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/posts\/5312\/revisions\/5661"}],"wp:attachment":[{"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/media?parent=5312"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/categories?post=5312"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/professor.norton.net.br\/index.php\/wp-json\/wp\/v2\/tags?post=5312"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}