SalesLogix Account/Contact Merged Log

[walkingTarget]

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.

[walkingTarget]

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

[tagersea · Accepted solution]

Try SET NOCOUNT ON. If the Trigger returns anything to SalesLogix it gets angry.