Identificando as Triggers do Database

Para executar o script, simplemente coloque-o no QA, altere o database AdventureWorks para o database onde deseja verificar a existência de triggers e tecle F5. Testado com SQL 2000/2005.

USE AdventureWorks
GO
SELECT
    Tabela = OBJECT_NAME(o.parent_obj),
    Trigger_name = o.[name],
    Tipo = CASE WHEN
           OBJECTPROPERTY(o.[id],
            ‘ExecIsInsteadOfTrigger’) = 1 THEN
                ‘Instead Of’            
           ELSE
            ‘After’
        END,
    [Insert] = CASE WHEN
        OBJECTPROPERTY(o.[id],
        ‘ExecIsInsertTrigger’) = 1 THEN
            ‘Yes’
        ELSE
            ‘No’
        END,
    [Update] = CASE WHEN
        OBJECTPROPERTY(o.[id],
        ‘ExecIsUpdateTrigger’) = 1 THEN
            ‘Yes’
        ELSE
            ‘No’
        END,
    [Delete] = CASE WHEN 
        OBJECTPROPERTY(o.[id],
        ‘ExecIsDeleteTrigger’) = 1 THEN
            ‘Yes’
        ELSE
            ‘No’
        END,
    [Enabled] = CASE WHEN
        OBJECTPROPERTY(o.[id],
        ‘ExecIsTriggerDisabled’) = 0 THEN
            ‘Yes’
        ELSE
            ‘No’
        END
FROM
  sysobjects o
WHERE
    OBJECTPROPERTY(o.[id], ‘IsTrigger’) = 1    
ORDER BY Tabela,Trigger_name
GO

Exemplo de Resultado:

Tabela          Trigger_name     Tipo       Insert Update Delete Enabled
————————————————————————
Address         uAddress         After      No     Yes    No     Yes
AddressType     uAddressType     After      No     Yes    No     Yes
AWBuildVersion  uAWBuildVersion  After      No     Yes    No     Yes
BillOfMaterials uBillOfMaterials After      No     Yes    No     Yes
Employee        dEmployee        Instead Of No     No     Yes    Yes
Vendor          dVendor          Instead Of No     No     Yes    Yes

Abraços


Nilton Pinheiro

Avaliação: