web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / SQL Trigger behavior o...
Microsoft Dynamics CRM (Archived)

SQL Trigger behavior on CRM 2011

(0) ShareShare
ReportReport
Posted on by Liran 145

Hi,

I'm a CRM newbie (but a veteran DBA) and I've gone through the forum and saw time and time again the advice not to use triggers on CRM.

I can understand the logic behind this, but - is there a way to implement a trigger on a base table without getting an annoying CRM application error ?

If not, what solution would you recommend on implementing a trace (to a trace table) updates in such entities as ContactExtensionBase ?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    It depends on what you're trying to accomplish, typically we would build a .NET plugin and subscribe to the appropriate events based on what was needed.  If your scope is SQL and you really need to get this done then you could build a sql job to poll the table using the last modified date > last job execution to get a list of records which have been changed.  You could cause SQL deadlocks with this approach depending on your setup though (even with(nolock)) so you will want to monitor that.  For that reason, I would use Filtered views (all core + extension tables are combined in View objects that begin with the word Filtered and in their definition use nolock; other views do not consistently provide nolock)

  • Liran Profile Picture
    Liran 145 on at

    Well, since I'm a DBA my scope is the SQL :)

    I've tried using an AFTER UPDATE DML trigger that simply writes the changes the the table using the INSERTED table.

    I've written a seperate DML trigger on the same table which simply updates certain columns with NULL and it works like a charm ! but my first trigger simply causes the CRM to put out an error each time I try to update (from the CRM app of course).

    I can't understand why the first trigger causes an error while the 2nd trigger works fine :(

    I've tried to trim down the trigger - simply to write the timestamp into a trace table each time there's an update ... same mysterious CRM app error.

    Any ideas ?

  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    Weird, if writing null works but writing getdate() to a Custom trace table column on a different database within the same sql instance doesn't work then I would think that was because of a constraint or data type spec on the tracetable.....I'm assuming you get the same result if you just try to flip a bit field from null to 0 or 1 on the trace table from within the trigger?

  • Community Member Profile Picture
    Community Member on at

    I forgot to mention; what you're doing is unsupported....Just an FYI in case you don't want to void your ability to request support from Microsoft....i.e. every support incident you start will begin by them having you remove all of your non-native database objects (if they accept the case).

  • Liran Profile Picture
    Liran 145 on at

    Well, this is a puzzle to me as well :)

    The trace table has no constraint and the datatype is correct - I've created it myself. Basically the same code (different names of course) of these two triggers behaves differently.

    If I adjust the 2nd trigger (which works) to update the ContactBaseExtention table to 1 instead of NULL it still works ! but the 1st trigger does not :( I'm quite baffled at this behaviour

  • Liran Profile Picture
    Liran 145 on at

    And yes, I'm aware of the lack of support issue. That's why I'd rather stick to SQL :)

  • Liran Profile Picture
    Liran 145 on at

    Can I perform this trace using a workflow / plugin ?

  • Liran Profile Picture
    Liran 145 on at

    First of all - thank you all for your replies ! :)

    What I need is very simple - I want to capture all updates to the ContactExtentionBase table to a seperate trace table (in another DB on the same server).

    The capture should be performed only if certain columns have been updated.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics CRM (Archived)

#1
SA-08121319-0 Profile Picture

SA-08121319-0 4

#1
Calum MacFarlane Profile Picture

Calum MacFarlane 4

#3
Alex Fun Wei Jie Profile Picture

Alex Fun Wei Jie 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans