Technical Article

Checks Triggers status on User- Databases.

,

Introduction:

SQL Server 7.0 introduced a new class of functions that return a wide range of property information that isn't readily available in SQL Server 6.5 and earlier versions.
These functions have three general classes: TypeProperty, which returns information about a data type;
ObjectProperty, which returns information about objects in the current database; and ColumnProperty, which returns information about a column or procedure parameter.
Together, these classes include dozens of property values you can query for database objects. Base on these power full class of function, I have put to gether a stored procedure that searches through user databases and
identify all disabled triggers in a particular database:

Audience:  DBA / SA.

use master
go

Create proc [dbo].[P_ZDBA_TRIGGER_STATUS_CHK]  @fpath varchar(255),@Email_address Varchar(50)
as
--*************************************************************************************************************************************                  
-- Date Created/User:06/24/2002 By Wali A. Ali .                                                
--
--SQL Server 7.0 introduced a new class of functions that return a wide range of property information 
--that isn't readily available in SQL Server 6.5 and earlier versions. 
--These functions have three general classes: TypeProperty, which returns information about a data type; 
--ObjectProperty, which returns information about objects in the current database; and 
--ColumnProperty, which returns information about a column or procedure parameter. 
--Together, these classes include dozens of property values you can query for database objects. 
--Base on these power full class of function. This stored procedure is written to search through user databases and 
--identify all disabled triggers in a particular database:
-- -------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- The procedure is located in Database Mgt\Client\DBA Maintenance Utilities                   
--                                                                                              
-- Utility Name:       P_ZDBA_TRIGGER_STATUS_CHK                                                
--                                                                                              
-- Category:         General T-SQL, Stored Procedure                                            
--                                                                                              
-- Utility Location: DBAMNT database on each Server.                                        
--                                                                                              
-- Execution description or syntax:  P_ZDBA_TRIGGER_STATUS_CHK  'f:\apps\dat\','SQLDBA'         
--                                                                                               
-- Result:           A Log file for each database in the following format is written;           
--                       [yyyymmddhhmm}_{Server-name}_TRGCHK.log]                       
--                                                                                               
-- Audience:         DBA                                                                           
--                                                                                              
-- Date Created: June, 2002                                                                 
--
-- Updates/Changes: Updated By      Reason
--------------------------------    -----------------      --------------------------------------------------------------------------------------------------------------
-- 6/24/02                       Wali. Ali          Original                                     
--------------------------------    -----------------      --------------------------------------------------------------------------------------------------------------             
-- Input Parameters:@PATH    varchar [Location where the TRIGGER CHECK log file will be written]
--
--@Email_address   varchar [Email address for the SQL DBAs. The 
--                                       procedure will send out e-mails to all DBAs when there is a disabled TRIGGER found for one or more  
--                                       user database.                  
--
-- Output Parameters:None
--
-- Called By:Normally invoked by a scheduled SQL Agent Job.                                                                     
--
-- Description/(Purpose): This stored procedure is written to search through user databases and 
--                                   identify all disabled triggers in a particular database:
-- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- Step by Step details:    
--
--    1:  Old TRIGER  log files for each database is removed, before the routine is started. 
--
--    2: All system databases are bypassed. [master, tempdb, model, msdb, pubs, northwind, distribution ]                                                                                               
--
--    3: The Log file showing the trigger status--[0 normal and 1 disabled trigger] is generated regardless. 
--                         
--    4:  When one or more trigger with status of 1 is found, the row(s)  is copied to a static work user table, 
--         and E-mail is composed to all SQL-DBA.  
--    
--  **************************************************************************************************************************************
SET NOCOUNT ON
SET QUOTED_IDENTIFIER OFF
SET ANSI_DEFAULTS OFF

Declare @command         as varchar(500),
            @sqlstmt             as varchar(500),
            @temptime          as varchar(21),
            @path                 as  varchar(500),       
            @SQLSTR          as varchar(500)

-- Step1: Delete  old log file
declare @delstmt as varchar(200)
set       @delstmt =  ' Del /q '  +  @fpath + '*_TRGCHK.log'
exec    @sqlstmt = master..xp_cmdshell  @delstmt

        
-- Create new log file-- [yyyymmddhhmm}_{Server-name}_TRGCHK.log]
SET @temptime=CONVERT(CHAR(21),GETDATE(),108)    -- 108 style
SET @temptime=SUBSTRING(@temptime,1,2)+SUBSTRING(@temptime,4,2)
--select @temptime --debugging
SET  @temptime=convert(char(8),getdate(),112)+@temptime  --112 style
-- select @temptime --debugging
SET  @path = @fpath  
SET  @path = @path  + @temptime  
SET  @path = @path + '_'
SET  @path = @path + '_' +@@servername+ '_' + '_TRGCHK.log'
select @path

-- drop and re-create global temp and work table.
if exists (select 1  from INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name = 'disabled' and table_type = 'BASE TABLE')
  drop table disabled
if exists (select 1  from tempdb.INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name like '##triggers' and table_type = 'BASE TABLE')
  drop table ##triggers

Begin
create table ##triggers (DB_Name sysname,  Trigger_Status int, Trigger_Name varchar(40),  Create_date datetime ) 
create table DBAMNT.dbo.disabled (DB_Name varchar(30),  Disabled_Trigger_Status int, Trigger_Name varchar(40),  Create_date datetime) 
End

exec sp_MSforeachdb '  USE ? insert into ##triggers SELECT "?" as ''DB-NAME'',OBJECTPROPERTY(object_id(obj.name),''ExecIsTriggerDisabled'') As ''Enable(0), DISable(1)'',right(obj.name,35) as ''Trigger Name'',
obj.crdate  as ''Create_Date'' FROM ?..sysobjects obj, master.dbo.sysdatabases db WHERE type = ''tr'' and db.name = ''?'' and db.name not  in (''master'' ,''model'' ,''tempdb'',  ''msdb'',''pubs'',''Northwind'', ''distribution'') and objectproperty(id,''IsMSShipped'') = 0 '

-- Step2: Write the contents of #triggers out to Trigger log.
SET @SQLSTR = '''BCP "SELECT right(DB_Name,30) as DB_Name, cast(Trigger_Status AS CHAR(1)), right(Trigger_Name,40), convert(char(21),create_date,100) from ##triggers " queryout ' + @path+'  -k -c -S'+@@servername+'  -T'''
SET @command  = 'echo * >> '  + @path
set @command    = 'master..xp_cmdshell ' 
set @command    = @command + @SQLSTR 
select @command
exec(@command)

-- check Triggers that might have disabled status--1 for any database.
Begin
insert into [DBAMNT].[dbo].[disabled] 
 select right(DB_Name,30) as DB_Name, Trigger_Status, Trigger_Name, create_date from ##triggers where Trigger_status = 1
end

select * from [DBAMNT].[dbo].[disabled]  -- during debugging.

-- If any trigger found with disabled status--1, notify  DBA unit via SQL-mail and the contents of disabled temp static table is attached.
declare @errcnt as int,
            @rcnt    as int
Begin
set @errcnt = (select count(*) from [DBAMNT].[dbo].[disabled]) 
set @rcnt   = @@rowcount
    if (@errcnt <> 0 and @rcnt <> 0)
      Begin
        SET NOCOUNT on
        SET @command = 'echo * >> ' + @path
        EXEC master.dbo.xp_cmdshell @command,NO_OUTPUT
        SET  @command= 'echo !!!! ERROR-TRIGGER: Disabled Trigger(s) found !!!!  >> '  + @path
        EXEC master.dbo.xp_cmdshell @command,NO_OUTPUT
        set  @sqlstmt = 'ERROR-TRIGGER: Disabled Trigger(s) found'
        set  @sqlstmt = @sqlstmt + ' ON: ' + UPPER(@@servername) 

-- Now Compose the E-mail notification 
Declare @dbname as varchar(30) 
set @dbname  = 'DBAMNT'
 exec master..xp_sendmail 
          @recipients     = @Email_address,
          @dbuse          = @dbname,
          @Query          = ' select * from [DBAMNT].[dbo].[disabled]',
          @message        = 'ERROR-TRIGGER: Disabled Triggers(s) Found, Check the Attachedment.',
          @subject            = @sqlstmt, 
          @attach_results = 'TRUE', @width = 250
       End
     Else
        SET  @command  = 'echo !!!! NO Disabled TRIGGERS are found !!!!  >> '  + @path
        EXEC master.dbo.xp_cmdshell @command,NO_OUTPUT

/* Last thing to do is to remove the disabled temp static table  and one temp table*/if exists (select 1  from DBAMNT.INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name = 'disabled' and table_type = 'BASE TABLE')
  drop table disabled
if exists (select 1  from tempdb.INFORMATION_SCHEMA.TABLES where table_schema = 'dbo' and table_name like '##triggers' and table_type = 'BASE TABLE')
  drop table ##triggers
END
Return (@@error) --end of P_Z_DBA_TRIGGER_STATUS_CHK

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Read 1,396 times
(19 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating