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

Activity

1. chadbaldwin commented on May 29, 2025

   

   Chad Baldwin (chadbaldwin)

   on May 29, 2025

   Author

   More actions

   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'