[SA 7.3.421 Pro] - Bug: Source Control

[SA 7.3.421 Pro] - Bug: Source Control

7.3 beta properly retrieves code for triggers, for example, the code shows

	Code:
	CREATE TRIGGER [legacy].[v_jaratok_INSTEAD_OF_INSERT]

However, changing that code and trying to push the changes into the database fails, because the trigger in Repo Browser is categorized as a procedure. The process stops when it tries to drop the trigger using DROP PROCEDURE instead of DROP TRIGGER. See the error message below:

	Quote:
	Can't delete object "legacy.v_jaratok_INSTEAD_OF_INSERT". Reason: A database error occurred while executing the following code: DROP PROCEDURE [legacy].[v_jaratok_INSTEAD_OF_INSERT] Errors:Msg 3705, Level 16, State 1, Cannot use DROP PROCEDURE with 'legacy.v_jaratok_INSTEAD_OF_INSERT' because 'legacy.v_jaratok_INSTEAD_OF_INSERT' is a trigger. Use DROP TRIGGER.

EDIT: Also, the file stored for a view also stores its triggers but changing that triggers only results in changing the state of the file storing the trigger. It should show the file for the view as modified, too.

Thank you very much for your bug report. I have opened a bug ticket for this issue.

Here is the question I'm passing back. Do you use factory default or customized version of "Objects" query?
In the intellisense popups do you see that trigger listed in the object's list? If yes, is it shown with procedure icon and type "procedure"

Yes, I use a custom"Objects (MSSQL) + Typed Synonyms" for the Objects query and was already investigating if it could be the cause for this issue. Unfortunately, it might be. Actually, it tricks Objects macro to believe that triggers are procedures so that I could select them from popup. I guess it would work properly if I reverted to the default one but I'd loose the ability to use popups for selecting triggers. Both features are very important to me.

May I request a feature that would introduce another object type for the $OBJECT()$, namely the trigger type? It would allow selecting triggers natively, without the modified Objects query.

Below it is the query:

Code:

if @@version like '%SQL Server 2008%'
or @@version like '%SQL Server 2012%'
or @@version like '%SQL Server 2014%'
or @@version like '%SQL Azure%'

select
name, CASE WHEN type = 'SN' THEN '^' +
CASE CAST(OBJECTPROPERTYEX(object_id,'BaseType') AS VARCHAR)
WHEN 'AF' THEN 'F'
WHEN 'FN' THEN 'F'
WHEN 'FS' THEN 'F'
WHEN 'FT' THEN 'U'
WHEN 'IF' THEN 'U'
WHEN 'IT' THEN 'T'
WHEN 'P' THEN 'P'
WHEN 'PC' THEN 'P'
WHEN 'TR' THEN 'P'
--WHEN 'TR' THEN 'TR'
WHEN 'S' THEN 'T'
WHEN 'SQ' THEN 'I'
WHEN 'TF' THEN 'U'
WHEN 'TT' THEN 'K'
WHEN 'U' THEN 'T'
WHEN 'V' THEN 'V'
WHEN 'X' THEN 'P'
ELSE ' '
END
ELSE CASE type WHEN 'TR' THEN 'P' ELSE type END END,
object_id, create_date, modify_date
from
[$DB_NAME$].sys.all_objects
where
type in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS', 'TR')
and (
schema_id = :SCHEMA_ID
or (
1 = :SCHEMA_ID
and user_name() = 'dbo'
and schema_id = 4
and name in ('sysobjects','sysindexes','syscolumns','systypes','syscomments','sysfiles1','syspermissions','sysusers','sysproperties','sysdepends','sysreferences','sysfulltextcatalogs','sysindexkeys','sysforeignkeys','sysmembers','sysprotects','sysfulltextnotify','sysfiles','sysfilegroups')
)
)
and not (name = 'dtproperties')
and not (name like 'dt%' and type = 'P')

union all

SELECT t.NAME + COALESCE(CHAR(0) + t1.name
+ case
when t1.name in ('varchar', 'char', 'binary', 'varbinary')
then '(' + case t1.max_length when -1 then 'max' else convert(varchar, t1.max_length) end + ')'
when t1.name in ('nvarchar', 'nchar')
then '(' + case t1.max_length when -1 then 'max' else convert(varchar, t1.max_length / 2) end + ')'
when t1.name in ('decimal', 'numeric')
then '(' + convert(varchar, t1.[precision]) + ',' + convert(varchar, t1.scale) + ')'
else ''
END, ''),
COALESCE(o.type, 'TD'),
COALESCE(tt.type_table_object_id, t.user_type_id),
o.create_date, o.modify_date
FROM [$DB_NAME$].sys.types t
LEFT JOIN [$DB_NAME$].sys.types t1 ON t.system_type_id = t1.user_type_id
LEFT JOIN [$DB_NAME$].sys.table_types tt ON tt.name = t.name
LEFT JOIN [$DB_NAME$].sys.objects o ON tt.type_table_object_id = o.[object_id]
WHERE t.is_user_defined = 1 and t.schema_id = :SCHEMA_ID

else
if @@version like '%SQL Server 2005%'

select
name, CASE WHEN type = 'SN' THEN '^' +
CASE CAST(OBJECTPROPERTYEX(object_id,'BaseType') AS VARCHAR)
WHEN 'AF' THEN 'F'
WHEN 'FN' THEN 'F'
WHEN 'FS' THEN 'F'
WHEN 'FT' THEN 'U'
WHEN 'IF' THEN 'U'
WHEN 'IT' THEN 'T'
WHEN 'P' THEN 'P'
WHEN 'PC' THEN 'P'
WHEN 'S' THEN 'T'
WHEN 'SQ' THEN 'I'
WHEN 'TF' THEN 'U'
WHEN 'TT' THEN 'K'
WHEN 'U' THEN 'T'
WHEN 'V' THEN 'V'
WHEN 'X' THEN 'P'
ELSE ' '
END
ELSE type END,
object_id, create_date, modify_date
from
[$DB_NAME$].sys.all_objects
where
type in ('U','S','V','P','X','RF','FN','TF','IF','SN','PC','FT','FS', 'TR')
and (
schema_id = :SCHEMA_ID
or (
1 = :SCHEMA_ID
and user_name() = 'dbo'
and schema_id = 4
and name in ('sysobjects','sysindexes','syscolumns','systypes','syscomments','sysfiles1','syspermissions','sysusers','sysproperties','sysdepends','sysreferences','sysfulltextcatalogs','sysindexkeys','sysforeignkeys','sysmembers','sysprotects','sysfulltextnotify','sysfiles','sysfilegroups')
)
)
and not (name = 'dtproperties')
and not (name like 'dt%' and type = 'P')

else

select
name, xtype, id, crdate, crdate
from
[$DB_NAME$].dbo.sysobjects
where
xtype in ('U','S','V','P','X','RF','FN','TF','IF')
and uid = :SCHEMA_ID
and not (name = 'dtproperties')
and not (name like 'dt%' and xtype = 'P')

I'm afraid these 2 things are incompatible. Substituting object type with incompatible type would throw source control interface off, as you can see in the case of triggers.

Yes, that's why introducing the object type trigger would come in handy.

Okay, I made some experiments with the original DB query for Objects and run into some serious issues which make me believe that you really, really should handle triggers on their own, as a separate object. Here's what I discovered.
With the factory version of the Object DB query:
#1. The code of the trigger is in the file for the table, which if fine, they're related after all.
#2. There's still no separate object for the trigger, which would come in handy in case it's the only thing that changed, and is an arguable decision (see #5 below).
#3. Changing the trigger and committing the changes now properly drops and recreates it instead of failing with DROP PROCEDURE, which was a side-effect of my tinkering with the DB query.

And now the bad ones:
#4. The ability to quickly retrieve trigger code from the editor is lost, since they no longer appear in popups.
#5. Whenever I change the trigger through the repo browser and commit the changes to the database, a disaster happens. The trigger is dropped and recreated properly and since the trigger code is coupled with the table code so is the table (even if it has not been changed), resulting in DATA LOSS. That's a critical error.

So restoring the original DB query only made things worse instead of fixing them.

I have reported this to the dev team, we are actively investigating it.

Thank you very much.

The is no "intelligent" solution available at this time. The scripts stored in the repository are by definition CREATE scripts, when applied to the database, they are applied as DROP and CREATE for existing objects and CREATE for new. Reconciliation of differences between the scripts and existing database design is almost like a rocket science, it's very unlikely the scripts can be reliably converted in database-commit-time to ALTER commands, there are way to many potential issues and complications. By design, the database source control is mostly a one way script from database to repository, and the other way is mostly for deploying changes to new environments or updating development schemas in continuous integration environments where unit tests of test-data generation projects can be used in conjunction to populate the recreated tables after each run.

We are adding a warning to the release version 7.3 to check for existing tables and display potential-data-loss message before executing the DROP and CREATE scripts for tables.