2 Replies Latest reply on Jun 20, 2013 7:34 AM by Jamie Cannon

    SQL Trigger for Network Login

    Jamie Cannon ITSMMVPGroup

      Hi everyone,

      I've hit a roadblock with some SQL code and need some help from some SQL guru's.  I have a trigger on my tps_user table that will set the network login based on the domain that is imported for a user via AD.  I've give it below:

       

      USE [Database_Name]

      GO

      /****** Object:  Trigger [dbo].[addnetworklogin]    Script Date: 06/17/2013 16:20:33 ******/

      SET ANSI_NULLS ON

      GO

      SET QUOTED_IDENTIFIER ON

      GO

      -- =============================================

      -- Author:        <Author,,Name>

      -- Create date: <Create Date,,>

      -- Description: <Description,,>

      -- =============================================

      ALTER TRIGGER [dbo].[addnetworklogin]

         ON [dbo].[tps_user]

         AFTER INSERT

      AS

      begin

       

            declare @Name as varchar(255)

          declare @Domain as varchar(255)

            declare @guid uniqueidentifier

            declare @networkLogin as varchar(255)

            declare @count int

       

            declare UserCursor cursor for

      SELECT tps_guid, tps_name, usr_domain

      FROM tps_user

           

            open UserCursor

       

            fetch next from UserCursor into @guid, @Name, @Domain

            while (@@FETCH_STATUS = 0)

            begin

      set @networkLogin = @Domain + '\' + @Name

      set @count = ''

      select @count = count(*) from tps_user_network_login

      where tps_network_login = @networkLogin

      group by tps_network_login

      print 'User: '+@networkLogin+', count: '+convert(varchar(10),@count)

           

            if @count = ''

      begin

      -- uncomment the following line if you want to limit all users to 1 network login.

      -- delete from tps_user_network_login where tps_user_guid = @guid

      insert into tps_user_network_login (tps_guid,tps_user_guid,tps_network_login)

      values (newid(),@guid,@networkLogin)

      end

      else update tps_user_network_login set tps_user_guid = @guid where tps_network_login = @networkLogin

       

      fetch next from UserCursor into @guid, @Name, @Domain

            end

       

            close UserCursor

            deallocate UserCursor

       

      end

       

       

      The code works great with one exception... if I am allowing the system to add accounts automatically if it doesn't recognize the email I get an error that it cannot allow NULLs.  I need to update this code to allow a static value to be added as the prefix for @domain for these cases.  I know it has to be pretty simple but I just don't know where to put it.

       

      So what I need is:

       

      if domain is NULL then network login should be "static_Domain\name"

       

      else the rest of the stuff that works. 

       

      Thanks!


        • 1. Re: SQL Trigger for Network Login
          Mroche SupportEmployee

          This trigger should do what you require (it will also only update the user that was inserted instead of all users)

           

          --=================================
          -- *** Author : MSR  ***
          -- *** Created : 18/07/2013 ***
          --=================================
          -- This trigger sets the network login for a user when the user is created.
          -- If there is aleady an existing network login for the new user the existing one will be updated.
          /*****************************************************************************************/
          SET ANSI_NULLS ON
          GO
          SET QUOTED_IDENTIFIER ON
          GO
          CREATE TRIGGER [i_tps_user_addnetworklogin] ON [dbo].[tps_user]
          FOR INSERT 
          AS
          begin
           declare @name as varchar(255)
           declare @domain as varchar(255)
           declare @guid uniqueidentifier
           declare @networkLogin as varchar(255)
           declare @count int
           
           select @guid = tps_guid, @name=tps_name, @domain=usr_domain
           from inserted
           begin
            -- Check to see if the domain is blank   
            if @domain is null
                   begin
            
             -- Set the domain for the user to DomainName as the user does not currently have a domain set
             set @domain='Enter Domain Name Here'
             UPDATE tps_user SET [email protected] 
            end
           set @networkLogin = @domain + '\' + @name
           set @count = ''
           select @count = count(*) from tps_user_network_login
            where tps_network_login = @networkLogin
            group by tps_network_login
                
           if @count = ''
           begin
            -- uncomment the following line if you want to limit all users to 1 network login.
            -- delete from tps_user_network_login where tps_user_guid = @guid
            insert into tps_user_network_login (tps_guid,tps_user_guid,tps_network_login)
             values (newid(),@guid,@networkLogin)
           end
            else update tps_user_network_login set tps_user_guid = @guid where tps_network_login = @networkLogin
           end
          end
          
          • 2. Re: SQL Trigger for Network Login
            Jamie Cannon ITSMMVPGroup

            Genius!  Thanks Martyn!