A question concerning agents used by companies on an accounting system

I work with a packaged accounting system that is distributed and supported to clients by my company. We are not the designers but we can make enhancements at the client's request by adding triggers to the tables at the back end.
There is an agents table and an agent system functions table that are related by an agent id. The functions table has a primary key constraint that comprises (1) agent id; (2) agent type and (3) system function id.
When a new agent is added the system automatically adds 742 new records to the functions table each with a unique key.

My job is to provide a trigger that will add the agent to multiple companies that are selected by the user adding a number to an unused field on the agent record that identifies the company. Each company will have it's own version of the accounting system but on the same server. Getting the agents trigger to add the agent to the companies is working but when the functions trigger comes into operation it falls over with the message "Violation of PRIMARY KEY constraint 'PK_btblAgentSystemFunctions'. Cannot insert duplicate key in object 'dbo_btblAgentSystemFunctions'. The duplicate key value is (258,A,1)".

Here follows the trigger:

 USE [Rugs Original Wholesale cc]
GO
/****** Object:  Trigger [dbo].[tr_Add_Agent_Function_to_Companies]    Script Date: 7/10/2015 3:39:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_Add_Agent_Function_to_Companies]
      ON [dbo].[_btblAgentSystemFunctions]
      AFTER INSERT
AS
      BEGIN
          declare @comments VARCHAR(1024)
            set @comments = (select cComments from _rtblAgents a inner join inserted i on a.idAgents = i.iAgentID)
          declare @ctagentid INT
            declare @whagentid INT
            declare @rsagentid INT
            
            set nocount on
            declare @count as INT = 1
                  
            while substring(@comments, @count, 2) <> ' '    
               begin
                   if   substring(@comments, @count, 2) = '01'
                        begin
                         
                             SET @ctagentid = (select MAX(idAgents) from [Rugs Original Cape Town cc].dbo._rtblAgents)
                               SET @whagentid = (select MAX(idAgents) from [Rugs Original Wholesale cc].dbo._rtblAgents)
                             INSERT INTO [Rugs Original Cape Town CC].[dbo].[_btblAgentSystemFunctions]
                          (iAgentID, cAgentType, iSystemFunctionID, iReadAccess,
                           iEditAccess, iAddAccess, iDeleteAccess, iRuleAccess)
                               SELECT
                                       @ctagentid, waf.cAgentType, waf.iSystemFunctionID, waf.iReadAccess,
                           waf.iEditAccess, waf.iAddAccess, waf.iDeleteAccess, waf.iRuleAccess
                               FROM [Rugs Original Wholesale cc].dbo._btblAgentSystemFunctions waf
                               WHERE iagentID = @whagentid
                                   --INNER JOIN [Rugs Original Wholesale cc].dbo._rtblAgents wa ON waf.iAgentID = wa.idAgents
                                   --INNER JOIN inserted i ON wa.idAgents = i.iAgentID
                              
                          end;

                     if   substring(@comments, @count, 2) = '02'
                          begin
                              SET @rsagentid = (select MAX(idAgents) from [Re-SA].dbo._rtblAgents)
                                SET @whagentid = (select MAX(idAgents) from [Rugs Original Wholesale cc].dbo._rtblAgents)
                                INSERT INTO [Re-SA].[dbo].[_btblAgentSystemFunctions]
                          (iAgentID, cAgentType, iSystemFunctionID, iReadAccess,
                           iEditAccess, iAddAccess, iDeleteAccess, iRuleAccess)
                                SELECT
                                       @rsagentid, waf.cAgentType, waf.iSystemFunctionID, waf.iReadAccess,
                           waf.iEditAccess, waf.iAddAccess, waf.iDeleteAccess, waf.iRuleAccess
                               FROM [Rugs Original Wholesale cc].dbo._btblAgentSystemFunctions waf
                               WHERE iagentID = @whagentid
                               --INNER JOIN [Rugs Original Wholesale cc].dbo._rtblAgents wa ON waf.iAgentID = wa.idAgents
                               --INNER JOIN inserted i ON waf.iAgentID = i.iAgentID
                          end
                     set @count = @count + 3
                    
               end
               set nocount off
      END

I have used two different methods to solve this problem and both give the same error, viz. (1) using inner joins and (2) identifying the latest agent id to get added using a SELECT MAX statement.
Philip van GassDatabase Developer/AdministratorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
Can an agent have different permissions / system functions for different companies?

If not try using if not exists (select 1 from dbo_btblAgentSystemFunctions where ...)

before the insert.

This might be coming from another trigger though.
0
Philip van GassDatabase Developer/AdministratorAuthor Commented:
Hi Kyle. Thanks for responding. As I was submitting this question I realized what the solution might be and I was correct. Here is the correctly working trigger:

 USE [Rugs Original Wholesale cc]
GO
/****** Object:  Trigger [dbo].[tr_Add_Agent_Function_to_Companies]    Script Date: 7/12/2015 12:15:15 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[tr_Add_Agent_Function_to_Companies]
      ON [dbo].[_btblAgentSystemFunctions]
      AFTER INSERT
AS
      BEGIN
          declare @comments VARCHAR(1024)
            set @comments = (select cComments from _rtblAgents a inner join inserted i on a.idAgents = i.iAgentID)
          declare @ctagentid INT, @whagentid INT, @rsagentid INT
            declare @whtype CHAR(1)
            declare @whfunction INT
            SET @whagentid = (select top 1 iAgentID from inserted)
            SET @whtype = (select top 1 cAgentType from inserted)
            SET @whfunction = (select top 1 iSystemFunctionID from inserted)
            
            set nocount on
            declare @count as INT = 1
                  
            while substring(@comments, @count, 2) <> ' '    
               begin
                   if   substring(@comments, @count, 2) = '01'
                        begin
                             SET @ctagentid = (select MAX(idAgents) from [Rugs Original Cape Town cc].dbo._rtblAgents)
                              
                             INSERT INTO [Rugs Original Cape Town CC].[dbo].[_btblAgentSystemFunctions]
                          (iAgentID, cAgentType, iSystemFunctionID, iReadAccess,
                           iEditAccess, iAddAccess, iDeleteAccess, iRuleAccess)
                               SELECT
                                       @ctagentid, waf.cAgentType, waf.iSystemFunctionID, waf.iReadAccess,
                           waf.iEditAccess, waf.iAddAccess, waf.iDeleteAccess, waf.iRuleAccess
                               FROM [Rugs Original Wholesale cc].dbo._btblAgentSystemFunctions waf
                               WHERE iagentID = @whagentid AND cAgentType = @whtype AND iSystemFunctionID = @whfunction    
                          end

                     if   substring(@comments, @count, 2) = '02'
                          begin
                              SET @rsagentid = (select MAX(idAgents) from [Re-SA].dbo._rtblAgents)
                                
                                INSERT INTO [Re-SA].[dbo].[_btblAgentSystemFunctions]
                          (iAgentID, cAgentType, iSystemFunctionID, iReadAccess,
                           iEditAccess, iAddAccess, iDeleteAccess, iRuleAccess)
                                SELECT
                                       @rsagentid, waf.cAgentType, waf.iSystemFunctionID, waf.iReadAccess,
                           waf.iEditAccess, waf.iAddAccess, waf.iDeleteAccess, waf.iRuleAccess
                               FROM [Rugs Original Wholesale cc].dbo._btblAgentSystemFunctions waf
                               WHERE  iagentID = @whagentid AND cAgentType = @whtype AND iSystemFunctionID = @whfunction
                          end
                     set @count = @count + 3
                    
               end
               set nocount off
      END

It's a great feeling when you struggle alone for a long time and then solve it !
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Philip van GassDatabase Developer/AdministratorAuthor Commented:
You can close this question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.