SQLtabledependency work fine when a update/delete or insert works. But if a trigger update the monitored data ??
glenfrendo opened this issue · 6 comments
SQLtabledependency work fine when a update/delete or insert works. But if a trigger updates the monitored data there is no notification back. Is this by design ??
Table B is monitoring via SqlTableDependency . TABLE A Triggers updates TABLE B. When changes occur via this mechanism , an update/delete or insert does not work. However if a SQL Statement via Studio is used or the row is modified. Then
SQLTableDependency works okay. An a appropriate notification is generated
For Information sake . The Permissions were set up was
EXEC sp_addrole 'sql_dependency_subscriber'
-- Permissions needed for [sql_dependency_subscriber]
GRANT SELECT to [sql_dependency_subscriber]
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [sql_dependency_subscriber]
GRANT RECEIVE ON QueryNotificationErrorsQueue TO [sql_dependency_subscriber]
GRANT REFERENCES on CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification] to [sql_dependency_subscriber]
GRANT ALTER to [sql_dependency_subscriber]
GRANT CONNECT to [sql_dependency_subscriber]
GRANT ALTER ON SCHEMA::dbo to sql_dependency_subscriber
GRANT CONTROL ON SCHEMA::dbo to sql_dependency_subscriber
GRANT CONTROL to sql_dependency_subscriber
GRANT CREATE CONTRACT to sql_dependency_subscriber
GRANT CREATE MESSAGE TYPE to sql_dependency_subscriber
GRANT CREATE PROCEDURE to [sql_dependency_subscriber]
GRANT CREATE QUEUE to [sql_dependency_subscriber]
GRANT CREATE to [sql_dependency_subscriber]
GRANT EXECUTE to sql_dependency_subscriber
GRANT VIEW DATABASE STATE to sql_dependency_subscriber
GRANT VIEW DEFINITION to [sql_dependency_subscriber]
-- Making sure that my users are member of the correct role.
EXEC sp_addrolemember 'sql_dependency_subscriber', 'subscribeUser'
@glenfrendo I had the same challenge. There is a setting on your SQL Server instance to check. In the advanced settings of the instance:
or
EXEC sys.sp_configure N'nested triggers'
This setting should not be changed on a whim as it has implications that need to be considered first. I have one instance where I have enabled this configuration and I am getting the changes I am expecting including the expected modifications done by the trigger.
Another consideration is the order triggers are fired. Since SqlTableDependency creates a trigger on the target table, if the table has another trigger on it, you will need to reason about what order the triggers should fire. This article might be helpful in that scenario.
One last consideration I had to make for my scenario (may or may not be applicable to you). The existing trigger on the table I wanted to monitor changes on called a stored proc. That stored proc returns the value of an identity column from the row that was inserted. The original design of the stored proc caused a null value to be passed back to the original trigger when another trigger was fired on that table. I cannot recall why this was exactly, but the solution was to use SCOPE_INDENTITY() in the stored proc to get the value of the identity column to return.
Hope this helps with your specific use case!
Yes that was it . Thanks for the confirmation. After down loading the appropriate SQLTabledependency source I found I could eliminate SQLtableDependency as having this issue. And could by setting up the appropriate triggers reproduce this issue. The question for me was, what was the default? . By default, the Nested Triggers option is enabled. Unfortunately this meant the system was changed for some reason. Again thanks for the confirmation. It was helpful.
Dear glenfrendo, fluxium
if you describe me the fix you implemented, i will apply to the source code and i will deploy a new NuGet package.
Thanks