2

Using SQLServer 2014+. The database i'm interested in, is part of a Merge Replication schema. Is there a way to detect in an insert trigger, if it was fired by an Application insert statement or a Replication-Agent insert trigger?

1

One way would be to inspect the APP_NAME() property inside the trigger.

I setup a simple merge publication and subscription, then created a "log" table with a trigger on the subscriber table to log the details.

IF OBJECT_ID(N'dbo.MergeLog', N'U') IS NOT NULL
DROP TABLE dbo.MergeLog;
CREATE TABLE dbo.MergeLog
(
    id int NOT NULL
        CONSTRAINT PK_MergeLog
        PRIMARY KEY CLUSTERED
    , AppName sysname NOT NULL
);
GO
IF OBJECT_ID(N'TestMergeTrigger', N'TR') IS NOT NULL
DROP TRIGGER TestMergeTrigger
GO
CREATE TRIGGER TestMergeTrigger
ON dbo.MergeReplSource
AFTER INSERT, UPDATE
AS
BEGIN
    INSERT INTO dbo.MergeLog (AppName, id)
    SELECT APP_NAME(), i.id
    FROM inserted i;
END
GO

After the merge ran, I saw the following in the dbo.MergeLog table:

╔════╦══════════════════════════════════════════════════════╗
║ id ║ AppName                                              ║
╠════╬══════════════════════════════════════════════════════╣
║ 1  ║ SourceServer-Test-MergeReplTestPublicat-DestServer-1 ║
╚════╩══════════════════════════════════════════════════════╝

The "AppName" column will reflect the names of the servers involved along with the merge replication publication name. Inspecting the APP_NAME() should allow you to definitively identify inserts against the subscriber from merge replication vs inserts from other apps.

One should be aware that the app name is simply a connection-string parameter set by the application itself, and as such can be "faked". Having said that, in order to provide the correct fake value, an attacker would need to know the name of the publication name, along with both the source and destination server names. The scenario is not very likely, but something to be aware of.

| improve this answer | |
  • Since the name of the replication task contains the server name in it, this did the trick: IF CHARINDEX(@@SERVERNAME, APP_NAME(), 1) = 0 ... BEGIN ... END – FaultyOverflow Sep 28 '17 at 15:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.