functions/Get-DbaTrigger.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
function Get-DbaTrigger {
    <#
.SYNOPSIS
Get all existing triggers on one or more SQL instances.
 
.DESCRIPTION
Get all existing triggers on one or more SQL instances.
 
Default output includes columns ComputerName, SqlInstance, Database, TriggerName, IsEnabled and DateLastModified.
 
.PARAMETER SqlInstance
The SQL Instance that you're connecting to.
 
.PARAMETER SqlCredential
SqlCredential object used to connect to the SQL Server as a different user.
  
.PARAMETER Database
The database(s) to process - this list is auto-populated from the server. If unspecified, all databases will be processed.
 
.PARAMETER ExcludeDatabase
The database(s) to exclude - this list is auto-populated from the server
 
.NOTES
Tags: Database, Triggers
Author: Klaas Vandenberghe ( @PowerDBAKlaas )
 
Website: https://dbatools.io
Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
.LINK
 https://dbatools.io/Get-DbaTrigger
 
.EXAMPLE
Get-DbaTrigger -SqlInstance ComputerA\sql987
 
Returns a custom object displaying ComputerName, SqlInstance, Database, TriggerName, IsEnabled and DateLastModified.
 
.EXAMPLE
Get-DbaTrigger -SqlInstance 'ComputerA\sql987','ComputerB'
 
Returns a custom object displaying ComputerName, SqlInstance, Database, TriggerName, IsEnabled and DateLastModified from two instances.
 
.EXAMPLE
Get-DbaTrigger -SqlInstance ComputerA\sql987 | Out-Gridview
 
Returns a gridview displaying ComputerName, SqlInstance, Database, TriggerName, IsEnabled and DateLastModified.
 
.EXAMPLE
'ComputerA\sql987','ComputerB' | Get-DbaTrigger | Out-Gridview
 
Returns a custom object displaying ComputerName, SqlInstance, Database, TriggerName, IsEnabled and DateLastModified from two instances.
 
#>

    [CmdletBinding()]
    param (
        [parameter(Mandatory = $true, ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlServer", "instance")]
        [DbaInstanceParameter[]]$SqlInstance,
        [Alias("Credential")]
        [PSCredential]
        $SqlCredential,
        [Alias("Databases")]
        [object[]]$Database,
        [object[]]$ExcludeDatabase
    )
    
    process {
        foreach ($Instance in $SqlInstance) {
            Write-Verbose "Connecting to $Instance"
            try {
                $server = Connect-SqlInstance -SqlInstance $Instance -SqlCredential $SqlCredential -Erroraction SilentlyContinue
            }
            catch {
                Write-Warning "Can't connect to $Instance"
                continue
            }
            
            Write-Verbose "Getting Server Level Triggers on $Instance"
            $server.Triggers |
                ForEach-Object {
                [PSCustomObject]@{
                    ComputerName     = $server.NetName
                    InstanceName     = $server.ServiceName
                    SqlInstance      = $server.DomainInstanceName
                    TriggerLevel     = "Server"
                    Database         = $null
                    TriggerName      = $_.Name
                    Status           = switch ($_.IsEnabled) { $true { "Enabled" } $false { "Disabled" } }
                    DateLastModified = $_.DateLastModified
                }
            }
            
            Write-Verbose "Getting Database Level Triggers on $Instance"
            $dbs = $server.Databases | Where-Object { $_.status -eq 'Normal' }
            
            if ($Database) {
                $dbs = $dbs | Where-Object Name -in $Database
            }
            if ($ExcludeDatabase) {
                $dbs = $dbs | Where-Object Name -notin $ExcludeDatabase
            }
            
            $dbs |
                ForEach-Object {
                $DatabaseName = $_.Name
                Write-Verbose "Getting Database Level Triggers on Database $DatabaseName on $Instance"
                $_.Triggers |
                    ForEach-Object {
                    [PSCustomObject]@{
                        ComputerName     = $server.NetName
                        InstanceName     = $server.ServiceName
                        SqlInstance      = $server.DomainInstanceName
                        TriggerLevel     = "Database"
                        Database         = $DatabaseName
                        TriggerName      = $_.Name
                        Status           = switch ($_.IsEnabled) { $true { "Enabled" } $false { "Disabled" } }
                        DateLastModified = $_.DateLastModified
                    }
                }
            }
        }
    }
}