SMO Scripting - Missing regular property NotForReplication property bag state Lazy for type Trigger

[chadbaldwin]

Not doing anything fancy, just scripting out tables with ScriptingOptions.Triggers enabled.

Prefetch grabs nearly everything, except for the NotForReplication field it seems. When trying to script out a table with triggers enabled, it still reaches out to the database to grab this field.

No idea if it's relevant as I have no clue how these files are structured/used (other than for generating the prefetch query), but maybe it has something to do with how it's configured in this file?

sqlmanagementobjects/src/Microsoft/SqlServer/Management/SqlEnum/xml/trigger.xml

Lines 66 to 68 in 17b6e32

	<version min_major='9' cloud_min_major='10'>
	<property name="NotForReplication" type="bit" mode="deploy">trr.is_not_for_replication</property>
	</version>

I noticed for some reason its mode is set to deploy instead of design like the other fields. Might have nothing to do with it, but figured I would point it out in case it's helpful.

[chadbaldwin]

I also tried using Server.SetDefaultInitFields() but that did not seem to help.

Full line used for testing:

svr.SetDefaultInitFields(typeof(Trigger), svr.DatabaseEngineEdition, ["NotForReplication"]);

Despite setting this prior to running prefetch, it still runs the following query for each trigger that is scripted out:

exec sp_executesql N'SELECT
tr.name AS [Name],
tr.object_id AS [ID],
tr.create_date AS [CreateDate],
tr.modify_date AS [DateLastModified],
CAST(
                tr.is_ms_shipped
            AS bit) AS [IsSystemObject],
CAST(ISNULL(OBJECTPROPERTYEX(tr.object_id,N''ExecIsAnsiNullsOn''),0) AS bit) AS [AnsiNullsStatus],
CAST(ISNULL(OBJECTPROPERTYEX(tr.object_id,N''ExecIsQuotedIdentOn''),0) AS bit) AS [QuotedIdentifierStatus],
CAST(CASE WHEN ISNULL(smtr.definition, ssmtr.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
case when amtr.object_id is null then N'''' else asmbltr.name end AS [AssemblyName],
case when amtr.object_id is null then N'''' else amtr.assembly_class end AS [ClassName],
case when amtr.object_id is null then N'''' else amtr.assembly_method end AS [MethodName],
case when amtr.object_id is null then case isnull(smtr.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end else case isnull(amtr.execute_as_principal_id, -1) when -1 then 1 when -2 then 2 else 3 end end AS [ExecutionContext],
case when amtr.object_id is null then ISNULL(user_name(smtr.execute_as_principal_id),N'''') else ISNULL(user_name(amtr.execute_as_principal_id), N'''') end AS [ExecutionContextPrincipal],
~trr.is_disabled AS [IsEnabled],
trr.is_instead_of_trigger AS [InsteadOf],
CAST(ISNULL(tei.object_id,0) AS bit) AS [Insert],
CASE WHEN tei.is_first = 1 THEN 0 WHEN tei.is_last = 1 THEN 2 ELSE 1 END AS [InsertOrder],
CAST(ISNULL(teu.object_id,0) AS bit) AS [Update],
CASE WHEN teu.is_first = 1 THEN 0 WHEN teu.is_last = 1 THEN 2	ELSE 1 END AS [UpdateOrder],
CAST(ISNULL(ted.object_id,0) AS bit) AS [Delete],
CASE WHEN ted.is_first = 1 THEN 0 WHEN ted.is_last = 1 THEN 2	ELSE 1 END AS [DeleteOrder],
CASE WHEN tr.type = N''TR'' THEN 1 WHEN tr.type = N''TA'' THEN 2 ELSE 1 END AS [ImplementationType],
trr.is_not_for_replication AS [NotForReplication],
ISNULL(sm.uses_native_compilation,0) AS [IsNativelyCompiled],
ISNULL(sm.is_schema_bound,0) AS [IsSchemaBound]
FROM
sys.tables AS tbl
INNER JOIN sys.objects AS tr ON (tr.type in (@_msparam_0, @_msparam_1)) AND (tr.parent_object_id=tbl.object_id)
LEFT OUTER JOIN sys.assembly_modules AS mod ON mod.object_id = tr.object_id
LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id
LEFT OUTER JOIN sys.assemblies AS asmbl ON asmbl.assembly_id = mod.assembly_id
LEFT OUTER JOIN sys.assembly_modules AS amtr ON amtr.object_id = tr.object_id
LEFT OUTER JOIN sys.assemblies AS asmbltr ON asmbltr.assembly_id = amtr.assembly_id
INNER JOIN sys.triggers AS trr ON trr.object_id = tr.object_id
LEFT OUTER JOIN sys.trigger_events AS tei ON tei.object_id = tr.object_id and tei.type=@_msparam_2
LEFT OUTER JOIN sys.trigger_events AS teu ON teu.object_id = tr.object_id and teu.type=@_msparam_3
LEFT OUTER JOIN sys.trigger_events AS ted ON ted.object_id = tr.object_id and ted.type=@_msparam_4
LEFT OUTER JOIN sys.all_sql_modules AS sm ON sm.object_id = tr.object_id
WHERE
(tr.name=@_msparam_5)and((tbl.name=@_msparam_6 and SCHEMA_NAME(tbl.schema_id)=@_msparam_7))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000),@_msparam_6 nvarchar(4000),@_msparam_7 nvarchar(4000)',@_msparam_0=N'TR',@_msparam_1=N'TA',@_msparam_2=N'1',@_msparam_3=N'2',@_msparam_4=N'3',@_msparam_5=N'MY_TRIGGER_NAME',@_msparam_6=N'MY_TABLE_NAME',@_msparam_7=N'MY_SCHEMA_NAME'

Just for documentation sake, this is the query run during prefetch:

exec sp_executesql N'SELECT
SCHEMA_NAME(tbl.schema_id) AS [Table_Schema],
tbl.name AS [Table_Name],
tr.name AS [Name],
trr.is_instead_of_trigger AS [InsteadOf],
CAST(ISNULL(tei.object_id,0) AS bit) AS [Insert],
CAST(ISNULL(ted.object_id,0) AS bit) AS [Delete],
CAST(ISNULL(teu.object_id,0) AS bit) AS [Update],
CASE WHEN tr.type = N''TR'' THEN 1 WHEN tr.type = N''TA'' THEN 2 ELSE 1 END AS [ImplementationType],
CAST(ISNULL(OBJECTPROPERTYEX(tr.object_id,N''ExecIsAnsiNullsOn''),0) AS bit) AS [AnsiNullsStatus],
CAST(ISNULL(OBJECTPROPERTYEX(tr.object_id,N''ExecIsQuotedIdentOn''),0) AS bit) AS [QuotedIdentifierStatus],
CAST(
                tr.is_ms_shipped
            AS bit) AS [IsSystemObject],
CASE WHEN ted.is_first = 1 THEN 0 WHEN ted.is_last = 1 THEN 2	ELSE 1 END AS [DeleteOrder],
CASE WHEN tei.is_first = 1 THEN 0 WHEN tei.is_last = 1 THEN 2 ELSE 1 END AS [InsertOrder],
CASE WHEN teu.is_first = 1 THEN 0 WHEN teu.is_last = 1 THEN 2	ELSE 1 END AS [UpdateOrder],
NULL AS [Text],
ISNULL(smtr.definition, ssmtr.definition) AS [Definition]
FROM
sys.tables AS tbl
INNER JOIN sys.objects AS tr ON (tr.type in (@_msparam_0, @_msparam_1)) AND (tr.parent_object_id=tbl.object_id)
LEFT OUTER JOIN sys.assembly_modules AS mod ON mod.object_id = tr.object_id
INNER JOIN sys.triggers AS trr ON trr.object_id = tr.object_id
LEFT OUTER JOIN sys.trigger_events AS tei ON tei.object_id = tr.object_id and tei.type=@_msparam_2
LEFT OUTER JOIN sys.trigger_events AS ted ON ted.object_id = tr.object_id and ted.type=@_msparam_3
LEFT OUTER JOIN sys.trigger_events AS teu ON teu.object_id = tr.object_id and teu.type=@_msparam_4
LEFT OUTER JOIN sys.sql_modules AS smtr ON smtr.object_id = tr.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmtr ON ssmtr.object_id = tr.object_id
ORDER BY
[Table_Schema] ASC,[Table_Name] ASC,[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'TR',@_msparam_1=N'TA',@_msparam_2=N'1',@_msparam_3=N'3',@_msparam_4=N'2'