10-26-2010 07:51 AM
For starters, I am new to SalesLogix and am still finding my way through Architect/Administrator examples, Google, and client usage. I've been reading some interesting threads, but this is my first post here; Hello, world! Now, on to the main event...
My company has requested that we log changes due to merging of contacts/accounts. My first hope was that someone had done this already and I could get an addon, but most of what I found by searching Google was related to Mail Merge and not helpful. My next thought was to intercept a Merge command in the client using Architect, but after reading these forums, I've discovered that I can't customize the List View grid as it is hardcoded.
I guess the option that I'm left with is creating a trigger on Contacts/Accounts/History/any other table affected by merge and writing some useful data to a Logging table. Later, I could use SQL to filter the rows by EntityID and recover some related information.
Do I have a good plan? Is there a better one? Any other comments?
Thanks in advance for reading/responses.
Solved! Go to Solution.
11-02-2010 09:07 AM
After researching several alternatives, I've come back to the idea of using SQL triggers. The triggers are simple enough to write, but I'm running into an annoying problem. I've created a trigger after DELETE on sysdba.CONTACT, which should fire when an contact is merged. Logging works fine if I delete a record in SQL Server Management Studio. In SalesLogix, however, merging contacts is broken; nothing happens. Even simply deleting a contact will not work. If I remove the trigger, SalesLogix returns to normal.
I figure that the missing link has something to do with SalesLogix. If you have any ideas, please let me know.
Here is the SQL for the DELETE trigger on sysdba.CONTACT:
USE [SalesLogix]
GO
CREATE TRIGGER TR_CONTACT_DELETE
ON [sysdba].[CONTACT]
AFTER DELETE
AS
BEGIN
DECLARE @WorkStation VARCHAR(255)
DECLARE @WindowsLogin VARCHAR(255)
DECLARE @IPs VARCHAR(255)
SET @WorkStation = HOST_NAME()
EXEC @WindowsLogin = sysdba.GetUserName @WorkStation
EXEC @IPs = sysdba.GetIPs @WorkStation
INSERT INTO [SLX_Logs].[dbo].[Merge_Log]
(id, table_name, date, row_deleted, workstation, ip_addresses, windows_login)
SELECT d.CONTACTID,
'CONTACT',
GETDATE(),
'DELETED - CONTACTID: ' + d.CONTACTID + ' ACCOUNTID: ' + d.ACCOUNTID,
@WorkStation,
@IPs,
@WindowsLogin
FROM DELETED AS d
END
GO
11-02-2010 03:36 PM