I got a request from one of my customers, they had a need to auto assign newly created AA codes to all users and that how the idea started, I saw the scripts created by Sivakumar earlier to auto assign all codes to all users and used his cross join to create the below trigger.
The trigger is simply works on AAG00401 table upon record insert, it assigns the newly created codes to all GP users automatically, just run below script over your company database and enjoy.
-- Author : Mohammad R. Daoud
-- Create date: 09:56 AM 07/12/2012
-- Description: This trigger as created to auto assign
-- newly created analytical accounting codes to all users.
CREATE TRIGGER DI_AutoAssignSecurity
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
DECLARE @aaTrxDimID INT
DECLARE @aaTrxDimCodeID INT
SELECT TOP 1 @aaTrxDimID = aaTrxDimID, @aaTrxDimCodeID = aaTrxDimCodeID FROM INSERTED
DELETE FROM AAG02000 WHERE aaTrxDimID = @aaTrxDimID AND AAG02000.aaTrxDimCodeID = @aaTrxDimCodeID
INSERT INTO AAG02000 (USERID, aaTrxDimID, aaTrxDimCodeID, aaDistribute, aaAdjust) (SELECT B.USERID, A.aaTrxDimID, A.aaTrxDimCodeID, 1, 1 FROM AAG00401 A CROSS JOIN DYNAMICS..SY01400 B WHERE A.aaTrxDimID = @aaTrxDimID AND A.aaTrxDimCodeID = @aaTrxDimCodeID)