As triggers podem ser do Tipo:

Funções do Oracle
Funções de valores simples:
Devolve o valor absoluto de (n).
o valor inteiro imediatamente superior ou igual a "n".
FLOOT(n) = Devolve o valor inteiro imediatamente inferior ou igual a "n".
MOD (m, n)= Devolve o resto resultante de dividir "m" entre "n".
NVL (valor, expressão)= Substitui um valor nulo por outro valor.
POWER (m, exponente)= Calcula a potência de um número.
ROUND (numero [, m])= Arredonda números com o número de dígitos de precisão indicados.
SIGN (valor)= Indica o signo do "valor".
SQRT(n)= Devolve a raiz quadrada de "n".
TRUNC (numero, [m])= Trunca números para que tenham uma certa quantidade de dígitos de precisão.
VAIRANCE (valor)= Devolve a média de um conjunto de valores.
ABS(n)=
CEIL(n)=Obtém
Funções de grupos de valores:
Calcula o valor médio de "n" ignorando os valores nulos.
Conta o número de vezes que a expressão avalia algum dado com valor não nulo
"*" conta todas as filas selecionadas.
MAX (expressão)= Calcula o máximo.
MIN (expressão)= Calcula o mínimo.
SUM (expressão)= Obtém a soma dos valores da expressão.
GREATEST (valor1, valor2…)= Obtém o maior valor da lista.
LEAST (valor1, valor2…)= Obtém o menor valor da lista.
AVG(n)=
COUNT (* | Expressão)=
Funções que devolvem valores de caracteres:
Devolve o caractere cujo valor em binário é equivalente a "n".
Devolve "cad1" concatenada com "cad2".
LOWER (cad)= Devolve a cadeia "cad" em minúsculas.
UPPER (cad)= Devolve a cadeia "cad" em maiúsculas.
INITCAP (cad)= Converte a cadeia "cad" a tipo título.
LPAD (cad1, n[,cad2])= Adiciona caracteres à esquerda da cadeia até que tenha uma certa longitude.
RPAD (cad1, n[,cad2])= Adiciona caracteres à direita até que tenha uma certa longitude.
LTRIM (cad [,set])= Suprime um conjunto de caracteres à esquerda da cadeia.
RTRIM (cad [,set])= Suprime um conjunto de caracteres à direita da cadeia.
REPLACE (cad, cadeia_busca [, cadeia_substitucao])= Substitui um caractere ou caracteres de uma cadeia com
caracteres.
SUBSTR (cad, m [,n])= Obtém parte de uma cadeia.
TRANSLATE (cad1, cad2, cad3)= Converte caracteres de uma cadeia em caracteres diferentes, segundo um
substituição marcado pelo usuário.
CHR(n) =
CONCAT (cad1, cad2)=
Funções que devolvem valores numéricos:
Devolve o valor ASCII da primeira letra da cadeia "cad".
Permite uma busca de um conjunto de caracteres em uma cadeia, mas
suprime nenhum caractere depois.
LENGTH (cad)= Devolve o número de caracteres de cad.
ASCII(cad)=
INSTR (cad1, cad2 [, comeco [,m]])=
Funções para o manejo de datas:
SYSDATE=
Devolve a data do sistema.
Devolve a data "data" incrementada em "n" meses.
ADD_MONTHS (data, n)=
1
Devolve a data do último dia do mês que contém "data".
Devolve a diferença em meses entre as datas "data1" e "data2".
NEXT_DAY (data, cad)= Devolve a data do primeiro dia da semana indicado por "cad" depois da data indic
"data".
LASTDAY (data)=
MONTHS_BETWEEN (data1, data2)=
Funções de conversão:
Transforma um tipo DATE ou NUMBER em uma cadeia de caracteres.
Transforma um tipo NUMBER ou CHAR em DATE.
TO_NUMBER= Transforma uma cadeia de caracteres em NUMBER.
TO_CHAR=
TO_DATE=
Trabalhando com Data no Oracle
A Oracle oferece através da linguagem PL/SQL muitos recursos para utilização e tratamento de datas. Este tipo de
importantíssimo na modelagem de um banco de dados. A maioria dos sistemas de informação precisa de atributos
manipulação de históricos ou até mesmo para registros cadastrais, e por isso merece cuidado muito especial, tanto
quanto na consulta e exibição.
O armazenamento e a consulta de datas necessita às vezes de recursos que o próprio SGBD oferece. Estes recurs
funções que contém parâmetros de formatação permitindo a manipulação do conteúdo da data, a extração de valor
consulta da data e hora atual configurada no servidor de banco de dados.
Este artigo tem o objetivo de mostrar as principais funções e formatos de data que o SBGD Oracle tem para oferece
uma abordagem rápida de como o Oracle armazena uma data, e em seguida o artigo irá descrever o uso das princi
de datas.
Formato de Data no SGBD Oracle
O Oracle armazena datas em um formato numérico, representando o século, ano, mês, dia, horas, minutos e segun
formato padrão de entrada e consulta para qualquer campo do tipo data é DD-MON-YY, ou seja, dia, mês e ano, se
mês aparece abreviado. As datas válidas no Oracle estão entre 1 de Janeiro de 4712 A.C. e 31 de Dezembro de 99
Executando Operações Aritméticas
Como já foi mencionado na seção anterior, o Oracle armazena datas em formato numérico. Desta forma fica fácil de
possível trabalhar operações aritméticas com datas. É permitido executar cálculos usando operadores como de adi
subtração. A tabela 1 mostra algumas operações que podem ser executadas e seus resultados.
Operação
DATA + NÚMERO
DATA – NÚMERO
DATA – DATA
DATA + (NÚMERO/24)
Resultado
DATA
DATA
NÚMERO DE DIAS
DATA
Descrição da Operação
Soma o número de dias para a DATA
Subtrai o número de dias de uma DATA
Subtrai uma DATA da outra.
Adiciona um número de horas na DATA
Tabela 1 – Operações Aritméticas com datas.
Formatos de Data permitidos pelo Oracle
Algumas funções do Oracle usam formatos para exibição e entrada de datas. As funções TO_CHAR() e TO_DA
exemplo, usam estes formatos. Abaixo segue a tabela 2 mostrando alguns destes formatos.
Formato
YYY ou YY
Y,YYY
Q
MM
DD
DAY
Descrição
Últimos três ou dois dígitos do ano
Ano com vírgula na posição indicada
Trimestre do Ano
Mês, valor de dois dígitos
Dia do Mês
Nome do dia da semana
2
HH24
MI
SS
Formato de hora
Formato de minutos
Formato de segundos.
Tabela 2 – Formatos de data permitidos pelo Oracle.
Função SYSDATE
SYSDATE é uma função de data que retorna a data e hora atual registrada no servidor de banco de dados. A funç
pode ser usada da mesma forma que qualquer outro nome de coluna existente no banco de dados. É possível exib
selecionando apenas a coluna contendo a função SYSDATE através de uma tabela interna do Oracle chamad
Nota: A tabela DUAL pode ser acessada por todos os usuários do banco de dados. A tabela DUAL contém apenas
uma linha. Esta tabela é útil quando se deseja consultar valores, como por exemplo, o valor de uma constante, pse
expressão que não é originada de uma tabela com dados do usuário. A listagem 1 mostra dois exemplos do uso da
Listagem 1. Exemplos do uso da tabela DUAL.
SELECT SYSDATE FROM DUAL;
SELECT TO_DATE('01/01/2005 00:01:00', 'DD/MM/YYYY HH24:MI:SS') + (6/24) FROM DUAL;
Função TO_DATE()
Sintaxe: TO_DATE(DATA_1, FORMATO)
Converte uma string de caractere para um formato de data.
Função MONTHS_BETWEEN()
Sintaxe: MONTHS_BETWEEN(DATA_1, DATA_2)
Calcula o número de meses entre duas datas. O resultado pode ser positivo ou negativo. Por exemplo, se a DATA
que a DATA_2, então o resultado será positivo.
Exemplo:
SELECT MONTHS_BETWEEN(TO_DATE('01/12/2004 00:00:00','DD/MM/YYYY HH24:MI:SS')
, TO_DATE('01/01/2001 00:00:00','DD/MM/YYYY HH24:MI:SS')) FROM DUAL;
Função ADD_MONTHS()
Sintaxe: ADD_MONTHS_BETWEEN(DATA, N)
Adiciona um número N de meses de calendário à DATA atribuída na função. O valor atribuído a N não pode ser ne
ser inteiro.
Exemplo:
SELECT ADD_MONTHS(TO_DATE('01/12/2004 00:00:00','DD/MM/YYYY HH24:MI:SS'), 5) FROM DUA
Função LAST_DAY()
Sintaxe: LAST_DAY(DATA)
Retorna a data do último dia do mês que está contido no valor atribuído no parâmetro DATA.
Exemplo:
SELECT LAST_DAY(TO_DATE('01/02/2005 00:00:00','DD/MM/YYYY HH24:MI:SS')) FROM DUAL;
Exibindo uma Data em um Formato Específico
Como foi dito anteriormente o formato padrão de exibição de datas no Oracle é DD-MON-YY. Caso seja necessá
3
exibição deste formato o Oracle oferece a função TO_CHAR, que permite formatar a data de várias form
Para utilizar a função TO_CHAR é necessário seguir algumas diretrizes. São elas:
1. Obedecer a seguinte sintaxe: TO_CHAR(DATA, ‘FORMATO’)
2. O formato deve estar entre apóstrofos e fazer distinção entre maiúsculas e minúsculas.
3. Deve haver separação por vírgulas entre o parâmetro DATA e o parâmetro FORMATO.
4. Os nomes de dias e meses na saída são preenchidos automaticamente por espaços
Exemplo:
SELECT TO_CHAR(SYSDATE, ‘DD/MM/YYYY HH24’) FROM DUAL;
4
Triggers
Gatilho ou trigger é um recurso de programação executado sempre que o evento
associado ocorrer.
É muito utilizada para ajudar a manter a consistência dos dados ou para propagar
alterações em um determinado dado de uma tabela para outras. Um bom exemplo é um
gatilho criado para controle de quem alterou a tabela, nesse caso, quando a alteração for
efetuada, o gatilho é "disparado" e grava em uma tabela de histórico de alteração, o
usuário e data/hora da alteração.
Exemplo: (MS-SQL Server)
CREATE TRIGGER nome_do_gatilho ON dono.Nome_da_tabela
FOR INSERT (ou UPDATE ou DELETE)
AS
Codigo para execucao
CONCEITOS:




O que são TRIGGERS;
Usos e aplicabilidade dos TRIGGERS;
Considerações e boas práticas em relação aos TRIGGERS;
Quando e como usá-los;
Primeiramente, vamos abordar o conceito central de TRIGGERS:
Um trigger é um tipo especial de procedimento armazenado, que é
executado sempre que há uma tentativa de modificar os dados de uma
tabela que é protegida por ele.
- Associados a uma tabela
Os TRIGGERS são definidos em uma tabela específica, que é denominada tabela de
TRIGGERS;
- Chamados Automaticamente
Quando há uma tentativa de inserir, atualizar ou excluir os dados em uma tabela, e
um TRIGGER tiver sido definido na tabela para essa ação específica, ele será
executado automaticamente, não podendo nunca ser ignorado.
- Não podem ser chamados diretamente
5
Ao contrário dos procedimentos armazenados do sistema, os disparadores não
podem ser chamados diretamente e não passam nem aceitam parâmetros.
- É parte de uma transação
O TRIGGER e a instrução que o aciona são tratados como uma única transação, que
poderá ser revertida em qualquer ponto do procedimento, caso você queria usar
“ROLLBACK”, conceitos que veremos mais a frente.
Orientações básicas quando estiver usando TRIGGER.
- As definições de TRIGGERS podem conter uma instrução “ROLLBACK
TRANSACTION”, mesmo que não exista uma instrução explícita de “BEGIN
TRANSACTION”;
- Se uma instrução “ROLLBACK TRANSACTION” for encontrada, então toda
a transação (o TRIGGER e a instrução que o disparou) será revertida ou desfeita.
Se uma instrução no script do TRIGGER seguir uma instrução “ROLLBACK
TRANSACTION”, a instrução será executada, então, isso nos obriga a ter uma
condição IF contendo uma cláusula RETURN para impedir o processamento de
outras instruções.
- Não é uma boa prática utilizar “ROLLBACK TRANSACTION” dentro de seus
TRIGGERS, pois isso gerará um retrabalho, afetando muito no desempenho de seu
banco de dados, pois toda a consistência deverá ser feita quando uma transação
falhar, lembrando que tanto a instrução quanto o TRIGGER formam uma única
transação. O mais indicado é validar as informações fora das transações com
TRIGGER para então efetuar, evitando que a transação seja desfeita.
- Para que um TRIGGER seja disparado, o usuário o qual entrou com as
instruções, deverá ter permissão de acessar tanto a entidade e consequentemente
ao TRIGGER.
Usos e aplicabilidade dos TRIGGERS




Impor uma integridade de dados mais complexa do que uma restrição
CHECK;
Definir mensagens de erro personalizadas;
Manter dados desnormalizados;
Comparar a consistência dos dados – posterior e anterior – de uma
instrução UPDATE;
Os TRIGGERS são usados com enorme eficiência para impor e manter integridade
referencial de baixo nível, e não para retornar resultados de consultas. A principal
vantagem é que eles podem conter uma lógica de processamento complexa.
Você pode usar TRIGGERS para atualizações e exclusões em cascata através de
tabelas relacionadas em um banco de dados, impor integridades mais complexas do
que uma restrição CHECK, definir mensagens de erro personalizadas, manter dados
desnormalizados e fazer comparações dos momentos anteriores e posteriores a
uma transação.
Quando queremos efetuar transações em cascata, por exemplo, um TRIGGER de
exclusão na tabela PRODUTO do banco de dados Northwind pode excluir os
registros correspondentes em outras tabelas que possuem registros com os
mesmos valores de PRODUCTID excluídos para que não haja quebra na
integridade, como a dito popular “pai pode não ter filhos, mas filhos sem um pai é
raro”.
6
Você pode utilizar os TRIGGERS para impor integridade referencial da seguinte
maneira:

Executando uma ação ou atualizações e exclusões em cascata:
A integridade referencial pode ser definida através do uso das restrições
FOREIGN KEY e REFERENCE, com a instrução CREATE TABLE. Os TRIGGERS
fazem bem o trabalho de checagem de violações e garantem que haja coerência
de acordo com a sua regra de negócios. Se você exclui um cliente, de certo,
você terá que excluir também todo o seu histórico de movimentações. Não seria
boa coisa se somente uma parte desta transação acontecesse.

Criando disparadores de vários registros:
Quando mais de um registro é atualizado, inserido ou excluído, você deve
implementar um TRIGGER para manipular vários registros.
CRIANDO TRIGGERS
As TRIGGERS são criadas utilizando a instrução CREATE TRIGGER que especifica a
tabela onde ela atuará, para que tipo de ação ele irá disparar suas ações seguido
pela instrução de conferência para disparo da ação.
As triggers podem ser do Tipo:
- Insert
-delete
-Update e podem ocorrer antes ou depois dos eventos que as dispararam.
Como elas são disparadas em tempo de execução e podem variar do
momento que estão sendo disparadas, elas trabalham com as variáveis :
:new e :old
7
Stored Procedure
Procedimento armazenado ou Stored Procedure é uma coleção de comandos em
SQL para dispensamento de Banco de dados. Encapsula tarefas repetitivas, aceita
parâmetros de entrada e retorna um valor de status (para indicar aceitação ou falha na
execução). O procedimento armazenado pode reduzir o tráfego na rede, melhorar a
performance, criar mecanismos de segurança, etc.
Exemplo: (MS-SQL Server)
Create procedure busca
@nomedebusca varchar (50)
as
select nome1, nome2
from nome_da_tabela
where nome = @nomedebusca
Fala pessoal, na coluna de hoje vou falar um pouco sobre Stored Procedures, um recurso
valioso para o desenvolvimento de aplicações e performance.
Stored Procedures nada mais são do que um conjunto de instruções Transact-SQL que são
executadas dentro do banco de dados. É como escrever um programa dentro do próprio banco
de dados para executar tudo lá dentro.
Dentro das Stored Procedures devemos utilizar comandos Transact-SQL que não deixam nada
a desejar a comandos de uma liguagem de programação qualquer, como Visual Basic ou
Delphi. O Transact-SQL possui instruções de comparação (if), loops (while) operadores,
variáveis, funcões, etc.
Vamos ver um exemplo:
CREATE PROCEDURE TESTE
AS
BEGIN
SELECT ‘O FAMOSO HELLO WORLD!’
END
Perceba que os comandos de início e término de bloco, BEGIN e END respectivamente, são
obrigatórios no início e fim do comando.
Bom , podemos receber parâmetros , e utilizarmos eles em instruções SQL que serão
executadas dentro da Stored Procedure:
CREATE PROCEDURE TESTE @PAR1 INT
AS
BEGIN
UPDATE TABELA1 SET CAMPO1 = ‘NOVO_VALOR’
WHERE CAMPO2 = @PAR1
END
8
Percebam que no exemplo acima , não utilizamos parênteses , pois Stored Procedures são um
pouco diferentes de funções.
Como uma Stored Procedure fica armazenada no banco de dados , ela ja é pre-compilada e o
SQL Server a executa mais rapidamente. Um exemplo de execução desta Stored Procedure ,
no Query Analyzer :
/* Chama a Stored Procedure TESTE passando 10 como primeiro parâmetro */
EXECUTE TESTE 10
Outra vantagem das Stored Procedures é que um programa chamador , seja ele uma página
ASP ou um programa em VB, Delphi, Java, etc, só precisa chamar o nome da Stored
Procedure, que pode conter diversos comandos Transact-SQL embutidos dentro dela, evitanto
assim um tráfego de rede maior, resultando em resposta mais rápida.
Uma Stored Procedure pode ainda retornar valores para a aplicação. Aqui temos um detalhe: o
SQL Server permite o retorno de dados em forma de uma tabela após a execução ou um valor
de retorno normal. Exemplo:
CREATE PROCEDURE TESTE @PAR1 INT
AS
BEGIN
SELECT @PAR1*@PAR1 AS QUADRADO
END
No exemplo acima a aplicação chamadora (cliente) pode capturar o retorno da Stored
Procedure através do campo chamado QUADRADO, que contém somente um valor de retorno:
o parâmetro elevado ao quadrado. Agora no próximo exemplo:
CREATE PROCEDURE TESTE @PAR1 INT
AS
BEGIN
SELECT CAMPO1 , CAMPO2 FROM TABELA1
WHERE CAMPO3 = @PAR1
END
Retorna para o cliente uma tabela contendo dois campos , CAMPO1 e CAMPO2, e podem ser
capturados pela aplicação chamadora como se fossem uma tabela.
O uso de Stored Procedure é encorajado, mais deve-se utilizar este recurso com cuidado pois
se utilizado em excesso o SQL Server pode ser sobrecarregado, mas ao mesmo tempo
podemos obter um ganho de performance considerável, dependendo do caso. Minhas
‘regrinhas’ para o uso de Stored Procedures:
* Não faça Stored Procedures que somente fazem um Select ou Update ou Delete. Para isso
envie a instrução diretamente.
* Use sempre transações, para poder ‘voltar’ os dados em caso de problemas
* Retorne somente o necessário, evitanto tráfego na rede desnecessário.
* Use uma nomeclatura coerente para as Stored Procedures e as variáveis dentro dela
* SEMPRE idente seu código ao entrar em uma estrutura de bloco.
* Comente o máximo possível do seu código através do -- ou do /* e */
9