Trigger synchronization between two tables in Microsoft SQL Server

To trigger synchronization between two tables in Microsoft SQL Server when the source table is updated, you can use SQL Server triggers. Triggers are special stored procedures that are automatically executed or fired in response to specific events, such as INSERT, UPDATE, or DELETE operations on a table.

Here's an example of how you can create a trigger to synchronize updates from a source table (SourceTable) to a destination table (DestinationTable):\

CREATE TRIGGER SyncTrigger

ON SourceTable

AFTER INSERT, UPDATE, DELETE

AS

BEGIN

    SET NOCOUNT ON;

    -- Check if the operation is an INSERT or UPDATE

    IF EXISTS(SELECT 1 FROM inserted)

    BEGIN

        -- Perform synchronization for INSERT or UPDATE

        MERGE INTO DestinationTable AS dest

        USING inserted AS src

        ON (dest.PrimaryKey = src.PrimaryKey)  -- Replace PrimaryKey with actual primary key column(s)

        WHEN MATCHED THEN

            UPDATE SET dest.Column1 = src.Column1,  -- Replace Column1 with actual column names

                       dest.Column2 = src.Column2   -- Replace Column2 with actual column names

        WHEN NOT MATCHED BY TARGET THEN

            INSERT (PrimaryKey, Column1, Column2)  -- Replace Column1, Column2 with actual column names

            VALUES (src.PrimaryKey, src.Column1, src.Column2);  -- Replace Column1, Column2 with actual column names

    END

    ELSE IF EXISTS(SELECT 1 FROM deleted)

    BEGIN

        -- Perform synchronization for DELETE

        DELETE FROM DestinationTable

        WHERE PrimaryKey IN (SELECT PrimaryKey FROM deleted);  -- Replace PrimaryKey with actual primary key column(s)

    END

END;

Explanation:

The trigger SyncTrigger is fired after INSERT, UPDATE, or DELETE operations on the SourceTable.

Within the trigger, it first checks if the operation is an INSERT or UPDATE by checking if the inserted pseudo table contains any records.

If it's an INSERT or UPDATE, it uses the MERGE statement to synchronize the data between the SourceTable and the DestinationTable. The MERGE statement performs both INSERT and UPDATE operations based on the data in the inserted table.

If it's a DELETE operation, it deletes corresponding records from the DestinationTable based on the data in the deleted pseudo table.

Make sure to replace placeholders like PrimaryKey, Column1, Column2 with actual column names in your tables. Also, adjust the synchronization logic according to your specific requirements and table structures.