Sandro Bizioli


Chi sogna di giorno conosce molte cose che sfuggono a chi sogna soltanto di notte. (E.A.Poe)
Mappa

Elenco di tutti i trigger presenti nel database

"Il trigger è un particolare tipo di stored procedure che viene attivata automaticamente quando i dati di una determinata tabella vengono modificati. I trigger vengono richiamati in risposta a istruzioni INSERT, UPDATE o DELETE, sono in grado di eseguire query su altre tabelle e possono contenere istruzioni Transact-SQL complesse..." (BOL - Book On Line)

Ad una tabella posso associare più trigger e decidere se renderli attivi oppure no.
Per avere informazioni sul trigger posso ricorrere all'istruzione sp_helpTrigger 'NomeTabella' che mi produrrà un output come questo:
/* Output
trigger_name   trigger_owner  isupdate   isdelete   isinsert  isafter  isinsteadof trigger_schema
-------------- -------------- ---------- ---------- --------- -------- ----------- --------------
tgMioTrigger   dbo            1          0          1         1        0           dbo
*/

Se sto utilizzando Enterprise Manager (EM) di SQL Server 2000, o di SQL Server Management Studio (SSMS) per SQL Server 2005 potrò visualizzare all'interno dell'ambiente grafico le informazioni del mio trigger in questo modo: 
 
Enterprise Manager
Espandere il gruppo di server selezionando un server dall'elenco, espandere Database ed il database a cui appartiene la tabella contenente il trigger e quindi fare clic su Tabelle. Nel riquadro dei dettagli fare clic con il pulsante destro del mouse sulla tabella contenente il trigger, scegliere Tutte le attività e quindi fare clic su Gestione trigger.

SQL Server Management Studio
Dall'Object Explorer espandere il nodo della connessione al server che ci interessa, espandere Database ed il database a cui appartiene la tabella contenente il trigger, quindi fare clic su Tabelle ed espandere la tabella in questione selezionando come ultimo il nodo Triggers. Una volta localizzato col tasto destro del mouse potrò decidere cosa fare.

Fin qui nulla di nuovo ed, infondo, abbastanza semplice dando per scontato di conoscere la tabella a cui è associato il/i notro/i trigger.
Ma se avessimo un database con molte tabelle e svariati trigger? Come faccio a ricordare o ad avere sotto controllo la situazione ad esempio di quelli attivi o di quelli solo di aggiornamento ecc. ecc.?
La sp seguente permette di interrogare il nostro database ed avere un resulset completo dei trigger presenti, della loro tipologica, del loro stato ed, ovviamente, il nome tabella a cui sono associati.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:  Sandro Bizioli (http://blogs.dotnethell.it/Sandro/)
-- Create date: 
-- Mod.   date: 
-- Description: Restituisce l'elenco e le proprietà dei trigger presenti
--    nel database permettendo di filtrare il resulset in base 
--    al nome della tabella o alle proprietà del trigger stesso.
-- Es. exec TriggersList @isUpdate = 1
--    Restituisce l'elenco di tutti i trigger For Insert
--  exec TriggersList @isUpdate = 1, @isInsert = 0
--    Restituisce l'elenco di tutti i trigger For Update ma non Insert
--              
-- =============================================
Alter procedure TriggersList
 ( @TableName varchar(25) = NULL,
  @IsAfter char(1)  = '%',
  @IsDelete char(1)  = '%',
  @IsInsert char(1)  = '%',
  @IsUpdate char(1)  = '%',
  @IsDisabled char(1)  = '%'
 )
as
IF @TableName is null 
   begin
 Select @TableName = '%'
   end
IF right(rtrim(@TableName), 1) <> '%' 
   begin
 Select @TableName = rtrim(@TableName) + '%'
   end
IF len(rtrim(@TableName)) > 25
   begin
 Select @TableName = left(rtrim(@TableName),24) + '%'
   end
SELECT
 T.name           as 'Trigger_Name',
 ObjectProperty(T.id, 'ExecIsUpdateTrigger')  as 'isUpdate',
 ObjectProperty(T.id, 'ExecIsDeleteTrigger')  as 'isDelete',
 ObjectProperty(T.id, 'ExecIsInsertTrigger')  as 'isInsert',
 ObjectProperty(T.id, 'ExecIsAfterTrigger')  as 'isAfter' ,
 ObjectProperty(T.id, 'ExecIsInsteadOfTrigger') as 'isInsteadOf',
 ObjectProperty(T.id, 'ExecIsTriggerDisabled') as 'IsDisabled',
 O.Name           as 'Table_Name'
FROM sysobjects T
 LEFT JOIN sysobjects O ON O.id = T.Parent_obj
WHERE T.type ='TR'
 AND O.Name           like @TableName
 AND ObjectProperty(T.id, 'ExecIsAfterTrigger')  like @IsAfter
 AND ObjectProperty(T.id, 'ExecIsInsertTrigger')  like @IsInsert
 AND ObjectProperty(T.id, 'ExecIsUpdateTrigger')  like @IsUpdate
 AND ObjectProperty(T.id, 'ExecIsDeleteTrigger')  like @IsDelete
 AND ObjectProperty(T.id, 'ExecIsTriggerDisabled') like @IsDisabled

Se ad esempio volessimo avere l'elenco di tutti i trigger presenti sul nostro database scriveremo:
exec TriggersList
/* Output
Trigger_Name    isUpdate  isDelete  isInsert  isAfter  isInsteadOf IsDisabled  Table_Name
--------------- --------- --------- --------- -------- ----------- ----------- -----------
tgNumero1       1         0         1         1        0           1           Tabella1
tgNumero2       1         0         0         1        0           0           Tabella2
tgNumero3       1         0         1         1        0           0           Tabella1
*/

Se ad esempio volessimo avere l'elenco di tutti i trigger presenti, ma non attivi scriveremo:
exec TriggersList @IsDisabled = 1
 
/* Output
Trigger_Name    isUpdate  isDelete  isInsert  isAfter  isInsteadOf IsDisabled  Table_Name
--------------- --------- --------- --------- -------- ----------- ----------- -----------
tgNumero1       1         0         1         1        0           1           Tabella1
*/

Per l'elenco di tutti i trigger attivabili sia sulla Update ma non sulla Insert:
exec TriggersList @IsInsert = 1, @IsDelete = 0
 
/* Output
Trigger_Name    isUpdate  isDelete  isInsert  isAfter  isInsteadOf IsDisabled  Table_Name
--------------- --------- --------- --------- -------- ----------- ----------- -----------
tgNumero2       1         0         0         1        0           0           Tabella2
*/

Spero che questa sp possa essere d'aiuto a qualcuno.
Categoria: SQL Server
mercoledì, 15 nov 2006 Ore. 12.03
Statistiche
  • Views Home Page: 105.748
  • Views Posts: 530.768
  • Views Gallerie: 88.833
  • n° Posts: 227
  • n° Commenti: 222
Copyright © 2002-2007 - Blogs 2.0
dotNetHell.it | Home Page Blogs
ASP.NET 2.0 Windows 2003