Updated Version of SQL Trigger to Sync DS Computer Group Structure with Active Directory

This is a modified version of CondorMan's SQL trigger to sync your DS Computer group structure with your AD OU structure. So first off, I just want to give full credit and thanks to CondorMan on the original work that he posted HERE.

I have made modifications to his trigger to handle some issues we were experiencing when we implemented it in our environment and also to add some extra functionality, but the main parts of the logic are all his, so please credit him for it. I thought about just posting this in a comment reply to his article but figured it would've looked pretty ugly and doing it here would be easier to read and give more space to discuss the changes.

Instructions to install trigger:

1. Open SQL Server Management Studio and connect to the server you want to implement this on.
2. Click File > Open > File... and select the attached ADSyncTrigger.sql file and click Open.
3. Click Execute to run the file, which creates the trigger in the eXpress database.

So, what's changed?

1. I added a condition so that the trigger would run if the last_inventory field was updated. The original trigger would run when msnet_dns_domain or msnet_domain_ou fields were updated. I added the condition for the last_inventory field as well so that we could force the trigger to run by running a Get Inventory job on a computer. We then have our inventory run every day on our DS consoles so that the structure will be synched up every day. This helped resolve instances where a computer was manually moved to another group, the ou/domain fields weren't changed so the trigger wouldn't run and these computers would remain stuck in the wrong folder.

   Code:

   IF (UPDATE(msnet_domain_ou) OR UPDATE(msnet_dns_domain) OR UPDATE(last_inventory))
   
   

2. I added check to see if the computer was both in an Automation session and in the New Computers group, if it is then the trigger will exit and not run. This was needed to resolve an issue we noticed where the trigger was interfering with the Initial Deployment job causing it not to run.

   Code:

   SELECT @CurrEnv = boot_env FROM sessions WHERE computer_id = (SELECT computer_id FROM INSERTED)
   If @CurrGroupID = -7 AND @CurrEnv = 1
   	Return	--Exit trigger
   	
   

3. I added a check to see if the msnet_dns_domain or msnet_domain_ou values are blank or NULL and if they are the trigger will put the computer in the root of the All Computers group and exit. This was to resolve issues where blank computer group names were being created for computers that were in the computers container in AD or when computers were in a workgroup.

   Code:

   If @GroupName Is Null OR @DomainOU Is Null OR @GroupName = '' OR @DomainOU = ''
   BEGIN
   UPDATE computer SET group_id = NULL WHERE computer_id = (SELECT computer_id FROM INSERTED)
   	Return	--Exit trigger
   END
   
   

4. I added a condition so that trigger doesn't attempt to delete the New Computers or All Computers groups. The original trigger was causing problems for us when a computer was the last computer in the New Computers group, because the trigger was trying to delete the group as it does for normal computer groups.

   Code:

   IF((SELECT COUNT(1) FROM computer WHERE group_id = @GroupID) < 2 AND @CurrGroupID != -7 AND @CurrGroupID Is Not Null)
   
   

5. Added various comments and made some formatting changes for easier readability.

Enjoy!