Aula IV – Functions e Triggers no MYSQL

Document Sample
Aula IV – Functions e Triggers no MYSQL Powered By Docstoc
					FTC – Faculdade de Tecnologia e Ciências
         Sistemas de Informação
               V Semestre


Aula IV – Functions e Triggers no 
            MYSQL




                Rafael Neiva
           rafael@prodados.inf.br
Functions

• CREATE FUNCTION sp_name ([parameter[,...]])
  [RETURNS type]
  [characteristic ...] 
  begin
      return variável
  end

  parameter:
    [ IN | OUT | INOUT ] param_name type
  characteristic:
      LANGUAGE SQL
    | [NOT] DETERMINISTIC
    | SQL SECURITY {DEFINER | INVOKER}
    | COMMENT string
Functions

• A cláusula RETURNS pode ser 
  especificada apenas por uma 
  FUNCTION. 
• É usada para indicar o tipo de 
  retorno da função, e o corpo da 
  função deve conter uma instrução 
  RETURN value.
Características

• Uma função é considerada 
  ``deterministica'' se ela sempre 
  retorna o mesmo resultado para os 
  mesmos parâmetros de entrada, e 
  “não deterministica'' caso contrário. 
Características

• A característica SQL SECURITY 
  pode ser usada para especificar se 
  a rotina deve ser executada usando 
  as permissões do usuário que criou 
  a rotina, ou o usuário que a chamou
Exemplo
•    create function getNivelString(nivel INT)
•    returns varchar(45)
•    begin
•      declare nivelString varchar(45);
•      case nivel
•        when 0 then nivelString = 'comum'
•        when 1 then nivelString = 'colaborador'
•        when 2 then nivelString = 'moderador'
•        when 3 then nivelString = 'administrador'
•        else nivelString = 'comum'
•      end;
•      return nivelString;
•   end
Exemplo pratico Function

• CREATE TABLE notas(aluno 
  VARCHAR(10), nota1 INT, nota2 
  INT, nota3 INT, nota4 INT);
• INSERT INTO notas VALUES('Maria', 
  10, 9, 10, 10); 
• INSERT INTO notas 
  VALUES('Pedro', 5, 2, 3, 4); 
Criar uma função pra 
calcular a média do aluno
CREATE FUNCTION media (nome VARCHAR(10))
RETURNS FLOAT 
DETERMINISTIC 
BEGIN 
  DECLARE n1,n2,n3,n4 INT; 
  DECLARE med FLOAT; 
  SELECT nota1,nota2,nota3,nota4 INTO n1,n2,n3,n4 
  FROM notas WHERE aluno = nome; 
  SET med = (n1+n2+n3+n4)/4; 
  RETURN med; 
END 
Alterar e Excluir SP e Functions

 • Alteração 
 • ALTER PROCEDURE | FUNCTION 
   sp_name ...
 • Exclusão
 • DROP PROCEDURE | FUNCTION 
   [IF EXISTS] sp_name
Trigger

• Um TRIGGER ou gatilho é um 
  objeto de banco de dados, 
  associado a uma tabela, definido 
  para ser disparado, respondendo a 
  um evento em particular
• Tais eventos são os comandos da 
  DML (Data Manipulation 
  Language): INSERT, REPLACE, 
  DELETE ou UPDATE.
Triggers

• Podemos definir inúmeros 
  TRIGGERS em uma base de dados 
  baseados diretamente em qual dos 
  comandos acima irá dispará-lo, 
  sendo que, para cada um, podemos 
  definir apenas um TRIGGER
• Os TRIGGERS poderão ser 
  disparados para trabalharem antes 
  ou depois do evento
Vantagens de utilizar 
Trigger
•   fornecem uma forma alternativa para verificar a 
    integridade
•   podem detectar, minimizar ou eliminar os erros de lógica 
    na base de dados
•    fornecem uma forma alternativa de executar tarefas 
    agendadas. 
•   você não tem que esperar para executar as tarefas 
    agendadas. 
•   Você pode lidar com essas tarefas antes ou após as 
    alterações feitas nas tabelas da base de dados.-
•    são muito úteis para auditar mudanças de dados numa 
    tabela da base de dados
Desvantagens
• pode apenas fornecer validação estendida, não 
  pode substituir todas as validações. 
• Algumas validações simples podem ser feitas no 
  nível de aplicativo. Por exemplo,você pode 
  validar ou verificar a entrada no lado cliente 
  usando JavaScript ou no lado do servidor pelo 
  script de servidor utilizando PHP ou ASP.NET.- 
• executam de forma invisível a relação cliente-
  servidor assim é difícil descobrir o que 
  aconteceu na camada de base de dados 
  subjacente
Importante 
•   Não é permitido chamar um stored procedure em um 
    trigger.
•   Não é permitido criar um trigger para uma view ou a 
    tabela temporária.
•   Não é permitido o uso de transações (commit e roolback) 
    num trigger.
•   A declaração de retorno (return) não é permitida num 
    trigger.
•   Criar um trigger para uma tabela da base de dados faz 
    com que o cache de consultas seja invalidado
•   Todos os triggers para uma tabela da base de dados deve 
    ter nome único.
Estrutura Trigger
•   CREATE
•   [DEFINER = { USER | CURRENT_USE } ]
•   TRIGGER trigger_name 
•   trigger_time trigger_event
•   On tbl_name FOR EACH ROW trigger_stmt
Estrutura Trigger
•   DEFINER: Quando o TRIGGER for disparado, esta opção 
    será checada para checar com quais privilégios este será 
    disparado. 
•    trigger_name: define o nome do procedimento, por 
    exemplo, trg_test;
•   trigger_time: define se o TRIGGER será ativado antes 
    (BEFORE) ou depois (AFTER) do comando que o 
    disparou;
•    trigger_event: aqui se define qual será o 
    evento, INSERT, REPLACE, DELETE ou UPDATE;
•   tbl_name: nome da tabela onde o TRIGGER ficará 
    “pendurado” aguardando o trigger_event;
•   trigger_stmt: as definições do que o TRIGGER deverá 
    fazer quando for disparado.
Definir Dados de 
OLD(antes) e NEW(depois)
• Em meio aos TRIGGERS temos 
  dois operadores importantíssimos 
  que nos possibilitam acessar as 
  colunas da tabela alvo do comando 
  DML, 
• podemos acessar os valores que 
  serão enviados para a tabela antes 
  (BEFORE) ou depois (AFTER) de 
  um UPDATE, 
Definir Dados de 
OLD(antes) e NEW(depois)
• INSERT: o operador NEW.nome_coluna, nos 
  permite verificar o valor enviado para ser 
  inserido em uma coluna de uma 
  tabela. OLD.nome_coluna não está disponível.
• DELETE: o operador OLD.nome_coluna nos 
  permite verificar o valor excluído ou a ser 
  excluído. NEW.nome_coluna não está 
  disponível.
• UPDATE: tanto OLD.nome_coluna 
  quanto NEW.nome_coluna estão disponíveis, 
  antes (BEFORE) ou depois (AFTER) da 
  atualização de uma linha.
Exemplo Trigger

• DELIMITER |
• CREATE TRIGGER exclui_tickets 
  AFTER DELETE ON USUARIOS
  FOR EACH ROW
  BEGIN
  DELETE FROM TICKETS WHERE 
  id_usuario = OLD.id_usuario;
  END|
• DELIMITER ;
Exemplo Prático Trigger
•   Criação da tabela de funcionário
•   CREATE TABLE `FUNCIONARIO`(
•   `codFuncionario` int(11) not null,
•   `nome` varchar(50) not null,
•   `sobrenome` varchar(50) not null,
•   `funcao` varchar(50) not null,
•   PRIMARY KEY (`cod_funcionario`)
•   );
Exemplo prático Trigger
• Sempre que o registro do funcionário mudar, 
  guardar as informações em outra tabela
• CREATE TABLE 
  `FUNCIONARIO_AUDITORIA`(
• `id` int(11) not null AUTO_INCREMENT,
• `codFuncionario` int(11) not null,
• `nome` varchar(50) not null,
• `data` datetime default null,
• `acao` varchar(50) default null,
• PRIMARY KEY (`id`)
• );
Exemplo prático Trigger
•   Criaremos uma trigger que será acionada sempre que 
    atualizar a tabela de funcionários 
•   DELIMITER $$
•   CREATE TRIGGER ` apos_atualizar`
•   BEFORE UPDATE ON `funcionario`
•   FOR EACH ROW begin
•   INSERT INTO `funcionario_auditoria` 
•   set acao = 'update', 
•   cod_funcionario  = OLD.cod_funcionario, 
•   nome = OLD.nome;
•   END $$
•   DELIMITER ;
Exemplo prático Trigger 

• Povoando a tabela funcionario
• INSERT INTO funcionario VALUES 
  (1,'Nasair','teste','professor');
• Testando a Trigger
  § Update funcionario
  § Set nome = 'rafael';
  § Where codFuncionario = 1;
• Verificando a operação feita
  § Select * from funcionario;
Administrar Triggers Mysql

• Verificar informações da Trigger 
  associado a uma base de dados e 
  pelo nome da trigger
• select * from 
  Information_schema.TRIGGERS
  WHERE
  Trigger_schema = ‘nome_banco' 
  and
  Trigger_name = ‘nome_trigger';
Administrar Triggers Mysql

• Verificar informações da Trigger 
  associado a uma base de dados e a 
  uma tabela
• select * from 
  Information_schema.TRIGGERS
  WHERE
  Trigger_schema = ‘nome_banco' 
  and
  Event_object_table = ‘nome_tabela';
Administrar Triggers Mysql

• Ver todas as Triggers
• Show triggers;
Deletar Trigger

• Drop trigger <nome_trigger>

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:50
posted:9/24/2013
language:
pages:27