Home Dashboard Directory Help
Search

SMO.script does not script a trigger state. Disabled triggers are not put back in a disabled state ! by ALZDBA


Status: 

Active


2
0
Sign in
to vote
Type: Bug
ID: 867404
Opened: 5/5/2014 2:40:34 AM
Access Restriction: Public
1
Workaround(s)
view
0
User(s) can reproduce this bug

Description

SMO.script does not script a trigger state. Disabled triggers are not put back in a disabled state !
A table trigger that has been disabled, will become enabled and remain enabled when this script is being executed.
This may cause failing DML when data is put into the table.

I tested this with SQL2008R2 an SQL2014
Details
Sign in to post a comment.
Sign in to post a workaround.
Posted by ALZDBA on 5/5/2014 at 2:43 AM
We coded a workaround to check all triggers state and added it after the "$returnValue = $transfer.ScriptTransfer()" statement:

    #Check disabled triggers
    $Script:DisabledTriggers = @()
    $db.tables | %{
        $CurrentTable = $_.Name
        $CurrentSchema = $_.Schema
        foreach ( $trg in $_.triggers ) {
            if ( $trg.Properties | WHERE-object { $_.Name -eq 'IsEnabled' -and $_.Value -eq $false } ) {
                $Script:DisabledTriggers += $('/* Disabled trigger !!! */
DISABLE TRIGGER [{0}] ON [{1}].[{2}] ;
GO
' -f $trg.Name, $CurrentSchema, $CurrentTable )
                }
            }
        }

        Out-File -InputObject $Script:DisabledTriggers -FilePath $CreationScriptOptions.FileName -Append