none
Alteracoes BD RRS feed

  • Pergunta

  • Gostaria de saber se no Sql Server 2005 tenho como criar alguma rotina que eu possa monitorar quando for feita alguma alteracao no BD? Recompilacao/Criacao de trigger, procedures, etc...

    Gostaria de receber notificacao via Email por exemplo...

    Junior


    Visual Studio com C# Sql Server
    segunda-feira, 29 de junho de 2009 22:45

Respostas

  • Olá Marcelo,

    Não possuo o Database Mail configurado para fazer um teste, mas acredito que não tenha problema.
    Estou vendo uma certa preocupação em relação a rodar SPs dentro de triggers DDL. Desconheço que exista alguma limitação.
    Fiz um pequeno teste abaixo e funcionou (embora não tenha utilizado as procedures de envio de e-mail)

    -- Cria a SP
    CREATE PROCEDURE uspFazAlgo
    AS
    PRINT 'Algo foi feito'
    GO
    -- Cria uma trigger que dispara a SP no Evento CREATE_DATABASE
    CREATE TRIGGER trgbd ON ALL SERVER
    FOR CREATE_DATABASE
    AS
    BEGIN
    EXEC uspFazAlgo
    END
    GO
    -- Cria um banco para forçar o disparo da trigger
    CREATE DATABASE DB
    -- Exclui o banco
    DROP DATABASE DB
    -- Exclui a SP
    DROP PROCEDURE uspFazAlgo


    Você sabe de alguma limitação nesse sentido, ou seja, procedures do Database Mail não podem ser executadas dentro de triggers DDL ?

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    O que é Cross Database Ownership Chaining ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!620.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta jr_81 quinta-feira, 24 de setembro de 2009 17:55
    quarta-feira, 1 de julho de 2009 10:29
  • Olá Júnior,

    Não há como criar triggers contra tabelas de sistema. É justamente para atender essa necessidade que existem as DDL Triggers. Enquanto as triggers normais (DML) disparam em reação a comandos DML (Insert, Update e Delete), as triggers DDL disparam em reação a eventos DDL (Create, Alter e Drop). É esse tipo de trigger que você precisa criar para controlar alterações em estrutura.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Certificados, Cross Database Ownership Chaining e Segurança
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!622.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta jr_81 quinta-feira, 24 de setembro de 2009 17:56
    terça-feira, 7 de julho de 2009 21:13
  • Junior...
    DDL_TRIGGER nao se cria em objetos do database e sim no proprio database ou server, veja um exemplo

    CREATE TRIGGER crk_teste ON database-- ALL SERVER --
    FOR DDL_TABLE_EVENTS --DDL_SERVER_LEVEL_EVENTS --
    AS
    BEGIN	
    DECLARE @Assunto VARCHAR(300), @Destinatario VARCHAR(300), @Texto VARCHAR(8000) 
    
    SELECT @Assunto = 'teste trigger', @Destinatario = 'junior@ssuark.com.br', @Texto = 'testando trigger' 
     
    --
    
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL', 
    @recipients = @Destinatario, 
    @subject = @Assunto, 
    @body = @Texto 
    
    --
    
    END
    GO


    Att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    • Marcado como Resposta jr_81 quinta-feira, 24 de setembro de 2009 17:56
    terça-feira, 7 de julho de 2009 21:14
    Moderador

Todas as Respostas

  • Ola Junior

     

    Sim é possivel, mas para isto vc precisa usar o Event Notification.

    Recentemente implantei esta solução, tomei como base o post abaixo (alterando o ddl_event)

    http://www.sqlservercentral.com/articles/SQLServerCentral/sqlserver2005logontriggers/2366/

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    terça-feira, 30 de junho de 2009 11:55
    Moderador
  • Marcelo,

    Concordo com sua sugestão.


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 30 de junho de 2009 12:58
    Moderador
  • Bom Dia,

    O uso do Event Notification é indicado, mas recomendo avaliar também a possibilidade de triggers DDL. Existem alguns prós e contras ambas as soluções. Recentemente escrevi um artigo sobre Event Notification (em português) para a revista Mundo .NET (está nas bancas). Lá demonstro inclusive como monitorar a criação, alteração e exclusão de objetos em qualquer banco de dados de uma dada instância.

    Vale a pena lembrar que embora o nome Event Notification seja bem sugestivo, ele não significa que as notificações serão enviadas por e-mail. Você terá que montar esse envio de e-mail a partir dos dados do banco de dados (o uso do Database Mail é bem indicado).

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    O que é Cross Database Ownership Chaining ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!620.entry
    Classifique as respostas. O seu feedback é imprescindível
    terça-feira, 30 de junho de 2009 13:21
  • Gustavo,

    Com o DDL_TRIGGER eu consigo executar uma proc p/ o evio de email?

    Att.

    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    terça-feira, 30 de junho de 2009 13:56
    Moderador
  • Marcelo,

    Não tenho certeza, mas acredito que sim!!!


    Mas se cada vez que for criado um novo trigger ou outro objeto disparar um outro trigger para chamar a procedure e enviar e-mail, nossa quantos processos o SQL Server irá realizar para poder realizar este procedimento.

    Será que não poderemos ter algum tipo de gargalo?

    Maia, o que você acha?


    Pedro Antonio Galvão Junior - MVP - Windows Server System - SQL Server/Coordenador de Projetos/DBA
    terça-feira, 30 de junho de 2009 14:02
    Moderador
  • Ola Junior

    Então pergunto isto pq acho que pela trigger ele não vai conseguir disparar uma proc dentro da trigger.

    Att.

    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    terça-feira, 30 de junho de 2009 14:48
    Moderador
  • Olá Marcelo,

    Não possuo o Database Mail configurado para fazer um teste, mas acredito que não tenha problema.
    Estou vendo uma certa preocupação em relação a rodar SPs dentro de triggers DDL. Desconheço que exista alguma limitação.
    Fiz um pequeno teste abaixo e funcionou (embora não tenha utilizado as procedures de envio de e-mail)

    -- Cria a SP
    CREATE PROCEDURE uspFazAlgo
    AS
    PRINT 'Algo foi feito'
    GO
    -- Cria uma trigger que dispara a SP no Evento CREATE_DATABASE
    CREATE TRIGGER trgbd ON ALL SERVER
    FOR CREATE_DATABASE
    AS
    BEGIN
    EXEC uspFazAlgo
    END
    GO
    -- Cria um banco para forçar o disparo da trigger
    CREATE DATABASE DB
    -- Exclui o banco
    DROP DATABASE DB
    -- Exclui a SP
    DROP PROCEDURE uspFazAlgo


    Você sabe de alguma limitação nesse sentido, ou seja, procedures do Database Mail não podem ser executadas dentro de triggers DDL ?

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    O que é Cross Database Ownership Chaining ?
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!620.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta jr_81 quinta-feira, 24 de setembro de 2009 17:55
    quarta-feira, 1 de julho de 2009 10:29
  • Ola Gustavo,

    Limitação em relação ao database mail nao, tive um problema no passado qndo fiz uma DDL trigger par monitorar logon, e precisava executar uma proc p/ fazer o kill da conexao e não rolou... tive que usar o rollback para matar a conexao, por isto me preocupei se a ddl trigger executaria uma proc.
    pelos seus testes acho que eu estava equivocado.

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    quarta-feira, 1 de julho de 2009 12:15
    Moderador
  • Ola Marcelo,

    Então implementei essa solução para testes e ela grava as informações porém tenho que monitorar ao inves de receber autmaticamente via Email por exemplo, quando alguem alterar meu BD.

    Nao achei uma maneira de fazer isso utilizando o Event Notification.

    Junior
    Visual Studio com C# Sql Server
    terça-feira, 7 de julho de 2009 19:42
  • Gustavo tentei implementar o sugerido mas me deparei com um problema, costumo utilizar a View Sys.Objects para checar meus objetos, essa trigger deveria ser criada na tabela que guarda a informacao correto?

    Qual seria a mesma no Sql Server 2005?

    Junior
    Visual Studio com C# Sql Server
    terça-feira, 7 de julho de 2009 19:44
  • Ola junior

    Vc tem o databasemail configurado no seu server?
    vc implementou o DDL trigger ou Event Notification?

    Att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    terça-feira, 7 de julho de 2009 19:47
    Moderador
  • Boa tarde Marcelo,

    Consegui enviar email direto da Trigger,

    Agora preciso saber qual a tabela no SQL 2005 que controla os objetos do BD.

    Junior
    Visual Studio com C# Sql Server
    terça-feira, 7 de julho de 2009 20:28
  • Junior.

    Acredito que a tabela que vc quer é a SYS.OBJECTS

    Att.
    Marcelo Fernandes


    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    terça-feira, 7 de julho de 2009 20:41
    Moderador
  • Entao Marcelo,

    Tenho criar a trigger conforme abaixo:

    create

     

    TRIGGER crk_teste

     

    ON sys.objects

    AFTER

    INSERT,DELETE,UPDATE

    AS

     

    BEGIN

     

    -- SET NOCOUNT ON added to prevent extra result sets from

     

    -- interfering with SELECT statements.

     

    SET NOCOUNT ON;

     

    DECLARE @Assunto VARCHAR(300),

    @Destinatario

    VARCHAR(300),

    @Texto

    VARCHAR(8000)

     

    SELECT @Assunto = 'teste trigger',

    @Destinatario

    = 'junior@ssuark.com.br',

    @Texto

    = 'testando trigger'

     

    --

     

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name

    = 'SQL',

    @recipients

    = @Destinatario,

    @subject

    = @Assunto,

    @body

    = @Texto

     

    --

    END

    GO


    E me reportu o seguinte erro:

    Msg 8197, Level 16, State 4, Procedure crk_teste, Line 2

    The object 'sys.objects' does not exist or is invalid for this operation.


    Utilizando outra tabela o sistema envia o email normalmente.

     


    Visual Studio com C# Sql Server
    terça-feira, 7 de julho de 2009 21:06
  • Olá Júnior,

    Não há como criar triggers contra tabelas de sistema. É justamente para atender essa necessidade que existem as DDL Triggers. Enquanto as triggers normais (DML) disparam em reação a comandos DML (Insert, Update e Delete), as triggers DDL disparam em reação a eventos DDL (Create, Alter e Drop). É esse tipo de trigger que você precisa criar para controlar alterações em estrutura.

    [ ]s,

    Gustavo Maia Aguiar
    http://gustavomaiaaguiar.spaces.live.com

    Certificados, Cross Database Ownership Chaining e Segurança
    http://gustavomaiaaguiar.spaces.live.com/blog/cns!F4F5C630410B9865!622.entry


    Classifique as respostas. O seu feedback é imprescindível
    • Marcado como Resposta jr_81 quinta-feira, 24 de setembro de 2009 17:56
    terça-feira, 7 de julho de 2009 21:13
  • Junior...
    DDL_TRIGGER nao se cria em objetos do database e sim no proprio database ou server, veja um exemplo

    CREATE TRIGGER crk_teste ON database-- ALL SERVER --
    FOR DDL_TABLE_EVENTS --DDL_SERVER_LEVEL_EVENTS --
    AS
    BEGIN	
    DECLARE @Assunto VARCHAR(300), @Destinatario VARCHAR(300), @Texto VARCHAR(8000) 
    
    SELECT @Assunto = 'teste trigger', @Destinatario = 'junior@ssuark.com.br', @Texto = 'testando trigger' 
     
    --
    
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL', 
    @recipients = @Destinatario, 
    @subject = @Assunto, 
    @body = @Texto 
    
    --
    
    END
    GO


    Att.
    Marcelo Fernandes
    MCP, MCDBA, MCSA, MCTS. Se útil, classifique!!!
    • Marcado como Resposta jr_81 quinta-feira, 24 de setembro de 2009 17:56
    terça-feira, 7 de julho de 2009 21:14
    Moderador
  • Galera implementei a solução conforme abaixo e rolou direitinho. Era bem isso que precisava.

    -- 1. Criando Trigger DDL para gravar qualquer alteração no BD
    CREATE TRIGGER [UDITORIA]
    ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
    As

    Declare @Dados as xml
    Set @Dados = eventdata()

     DECLARE @Assunto VARCHAR(300),
      @Destinatario VARCHAR(300),
      @Copia VARCHAR(2000),
      @Texto VARCHAR(MAX),
      @erro varchar(MAX)

    SELECT @Assunto = 'Alteração na Base ',
     @Destinatario = 'email@email.com.br',
     @Texto = 'Ocorreu um alteracao no BD, conforme abaixo: '+CHAR(13)+CHAR(13)+
        'Evento: ' + CONVERT(CHAR(254),@Dados.query('data(//EventType)'))+CHAR(13)+
        'Tipo Objeto: ' + CONVERT(CHAR(254),@Dados.query('data(//ObjectType)'))+CHAR(13)+
           'Usuario: ' + CONVERT(CHAR(254),@Dados.query('data(//LoginName)'))+CHAR(13)+
        'Objeto: ' + CONVERT(CHAR(254),@Dados.query('data(//ObjectName)'))+CHAR(13)+
        'Data: ' + CONVERT(char(30),@Dados.query('data(//PostTime)'))+CHAR(13)++CHAR(13)

    EXEC msdb.dbo.sp_send_dbmail
     @profile_name =  'Perfil,
     @recipients =  @Destinatario,
     @copy_recipients = @Copia,
     @subject = @Assunto,
     @body = @Texto,
     @importance = 'High'
     
     
    Obrigado a todos e desculpa pela demora de retorno, estava ausente nesse periodo.

    Junior


    Visual Studio com C# Sql Server
    quinta-feira, 24 de setembro de 2009 17:59