Source control does not get triggers?

I was testing out my SVN repository and I added a comment to a trigger. The table itself updated when I said update workspace from Database, but when you compare the table (which is in red) to the previous version it shows zero differences.

Where are the triggers being stored?

I'm not completely sure, need to experiment with this a bit more, but it looks like you would need to alter the Objects query in SA settings to get triggers listed in the Objects list, then they will be picked by the Source Control Browser.

For the references, see http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=23720&highlight=triggers
Please note that query is from version 6.2 and is a bit outdated. If you copy and paste it as is, you may loose some of the more recent functionality

Ok I had to add:

	Code:
	WHEN 'TR' THEN 'P'

To the long case statement

and change the else to:

	Code:
	ELSE CASE type WHEN 'TR' THEN 'P' ELSE type END END,

That was it. hope this doesn't impact anything.

Is this a bug via omission in the object query+typed synonyms query?

Seems like a side effect of this might be that triggers now show up in the popup when writing queries.

	Mindflux wrote:
	Seems like a side effect of this might be that triggers now show up in the popup when writing queries.

I've done that quite a long time ago to be able to use my 'edit' snippet to retrieve triggers into editor, either one by one or grouped by table.

	gemisigo wrote:
	I've done that quite a long time ago to be able to use my 'edit' snippet to retrieve triggers into editor, either one by one or grouped by table.

SysOp had me change some code in the Objects (MSSQL) + Typed Synonyms DB Query. I thought I had seen some triggers show up in my select ctrl+space statements.. but I'm not seeing them now.

Before I was having issues where SCS wasn't getting triggers and putting them into my workspace or repos.

	Mindflux wrote:
	SysOp had me change some code in the Objects (MSSQL) + Typed Synonyms DB Query. I thought I had seen some triggers show up in my select ctrl+space statements.. but I'm not seeing them now.

Yes, the topic he was referred to was the one where I asked for that feature. And while it was modified in 6.2 not much has changed in that DB Query in the last version according my check (which was not a thorough one, I must admit). Though I've got no idea why you cannot see them anymore.

So along the same vein.. I made a temp table, gave it some permissions and synced it with the source control.

I then changed the table structure and then tried "update database from repository" for that table.

It remade my table, since it's a drop/create script.. but all my user permissions were gone from it, so I could no longer run a select or insert query. (We use SQL server permissions, not AD).

Looking at the .SQL files, I don't see the permissions listed.. but if I open the DDL for a table via the popup in editor windows I can see all the appropriate permissions there.

Here is another way to skin the cat. On DB Options tab in the Options dialog, select SQL Assistance group, then SQL Server on the left. On the right, there will be an option to to specify external DDL Extraction utility. Here you can specify command line to external batch file or exe. When reverse engineering object DDL from database for the workspace files, as well as in some other places, SA will call your command and pass the following input parameters

	Code:
	%1 - server name %2 - server port %3 - user name %4 - user password %5 - db name %6 - schema name %7 - object name %8 - object type (TABLE,VIEW,MVIEW,PROCEDURE,FUNCTION,SYNPUBLIC,SYNONYM,SEQUENCE,TYPE,PACKAGE)

Whatever the invoked command outputs to the standard output, would be accepted as the returned object DDL. The command must complete with exit code 0 to indicate succesfull processing. Any other exit code would be treated as an error. This way you can use an external tool or a batch file recursively calling OSQL.EXE command or ISQL command that can take object name as a parameter and return the required DDL which could contain table DDL along with the triggers. The resulting script would be then saved in the workspace as a single table DDL file.

SysOp wrote:

Here is another way to skin the cat. On DB Options tab in the Options dialog, select SQL Assistance group, then SQL Server on the left. On the right, there will be an option to to specify external DDL Extraction utility. Here you can specify command line to external batch file or exe. When reverse engineering object DDL from database for the workspace files, as well as in some other places, SA will call your command and pass the following input parameters

	Code:
	%1 - server name %2 - server port %3 - user name %4 - user password %5 - db name %6 - schema name %7 - object name %8 - object type (TABLE,VIEW,MVIEW,PROCEDURE,FUNCTION,SYNPUBLIC,SYNONYM,SEQUENCE,TYPE,PACKAGE)

Whatever the invoked command outputs to the standard output, would be accepted as the returned object DDL. The command must complete with exit code 0 to indicate succesfull processing. Any other exit code would be treated as an error. This way you can use an external tool or a batch file recursively calling OSQL.EXE command or ISQL command that can take object name as a parameter and return the required DDL which could contain table DDL along with the triggers. The resulting script would be then saved in the workspace as a single table DDL file.

Ok that's good to know. I had seen the DDL extraction utility setting before but never looked into it.

Are you saying there's no way (at present) for the SCS logic to get perms? Another couple of source tools I've been testing seem to grab that stuff... but don't integrate with any intellisense tools.

I think there is. I guess the SCS uses the DDL Code (in DB Options > DB Queries) to fetch the DDL code that's to be stored in the file. You could alter that to retrieve user permissions as well and add it to the generated text as sql statements. I'd do it myself (I'll need to take care some of these things there since they got out of control :) ) but I have to leave now to catch my bus. I'll do it tomorrow though.

gemisigo wrote:

I think there is. I guess the SCS uses the DDL Code (in DB Options > DB Queries) to fetch the DDL code that's to be stored in the file. You could alter that to retrieve user permissions as well and add it to the generated text as sql statements. I'd do it myself (I'll need to take care some of these things there since they got out of control :) ) but I have to leave now to catch my bus. I'll do it tomorrow though.

The DDL that pulls up when hovering over a table name in an editor window and clicking "code" has the permissions in it. It's just the SCS DDL dump that does not. The triggers, however are omitted from the DDL when clicking "code"... I'd think that'd pull in too since it's part of that table.

	Mindflux wrote:
	The DDL that pulls up when hovering over a table name in an editor window and clicking "code" has the permissions in it. It's just the SCS DDL dump that does not. The triggers, however are omitted from the DDL when clicking "code"... I'd think that'd pull in too since it's part of that table.

That's interesting. It retrieves code for objects other than tables but I only get message

	Quote:
	/* Cannot find source code for 'dbo.afa'. Check the "DDL Code (MSSQL)" query in SQL Assistant Options. */

if I try that on a table.

gemisigo wrote:

	Mindflux wrote:
	The DDL that pulls up when hovering over a table name in an editor window and clicking "code" has the permissions in it. It's just the SCS DDL dump that does not. The triggers, however are omitted from the DDL when clicking "code"... I'd think that'd pull in too since it's part of that table.

That's interesting. It retrieves code for objects other than tables but I only get message

	Quote:
	/* Cannot find source code for 'dbo.afa'. Check the "DDL Code (MSSQL)" query in SQL Assistant Options. */

if I try that on a table.

So is that a bug in the 6.4 branch? Unable to pull table ddl from popup?

I don't know if it ever worked for me, I haven't tried till you mentioned it. But checking the DB Query I can see that it queries the dbo.syscomments, which is a backward compatibility view for SQL 2k. As far as I remember it contains sql definitions for views, procedures, functions, etc. but it has nothing to do with tables. So if that works for you then you must have something else there. Of course, it might be remnant from earlier versions. This one is what I have in effect for SQL Server:

Code:

if @@version like '%SQL Server 2005%'
or @@version like '%SQL Server 2008%'
or @@version like '%SQL Server 2012%'
IF EXISTS (SELECT * FROM [$DB_NAME$].sys.synonyms WHERE object_id = :OBJECT_ID)
   SELECT 'CREATE SYNONYM [' + s.name + '].[' + o.name + '] FOR ' + o.base_object_name
   FROM [$DB_NAME$].sys.synonyms o
   JOIN [$DB_NAME$].sys.schemas s ON s.schema_id = o.schema_id
   WHERE o.object_id = :OBJECT_ID
ELSE
   SELECT text
   FROM [$DB_NAME$].dbo.syscomments
   WHERE id = :OBJECT_ID
   ORDER BY colid
ELSE
SELECT text
FROM [$DB_NAME$].dbo.syscomments
WHERE id = :OBJECT_ID
ORDER BY colid