You might be slightly over complicating a simple thing, look through this example
😎
USE tempdb;
GO
/*
Sample Schema, Client and Transactions
*/
/* Drop the tables if they exist in the database */
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'TBL_HEADER'
AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.TBL_HEADER;
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = N'TBL_CLIENT'
AND TABLE_SCHEMA = N'dbo')
DROP TABLE dbo.TBL_CLIENT;
/* Create simplified transaction table */
CREATE TABLE dbo.TBL_HEADER
(
HEADER_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,CLIENT_ID INT NOT NULL
,HEADER_DATE DATETIME2(0) NOT NULL DEFAULT(SYSDATETIME())
,HEADER_TRANSACTIONDATE DATETIME2(0) NOT NULL
);
/* Create simplified client table */
CREATE TABLE dbo.TBL_CLIENT
(
CLIENT_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL
,CLIENT_NAME NVARCHAR(128) NOT NULL
,CLIENT_TRANSACTIONDATE DATETIME2(0) NOT NULL
);
GO
/*
Now for the fun stuff, this is a trigger that updates the
client table every time a new transaction is inserted or
updated in the transaction table. It ignores deletes as
the DELETE keyword is missing in the scope definition which
is the line directly after the target object name
(ON dbo.TBL_HEADER)
The trigger uses the two pseudo tables, [inserted] and [deleted].
For inserts, the inserted values are found in the [inserted]
table but the [deleted] is empty.
For updates, the [inserted] holds the new values and the
[deleted] has the old values. If the value is unchanged,
the trigger ignores the record.
*/
CREATE TRIGGER dbo.TRG_HEADER_UPDATE_CLIENT_TRANSACTIONDATE
/* Schema qualified target object name */
ON dbo.TBL_HEADER
/* DML scope, which data manipulation events will fire the
trigger
*/
AFTER INSERT, UPDATE
AS
/*
Trigger code
*/
UPDATE CL
SET CL.CLIENT_TRANSACTIONDATE = INS.HEADER_TRANSACTIONDATE
FROM inserted INS
INNER JOIN dbo.TBL_CLIENT CL
ON INS.CLIENT_ID = CL.CLIENT_ID
LEFT OUTER JOIN deleted DE
ON CL.CLIENT_ID = DE.CLIENT_ID
/* For inserts there are no records in the [deleted] pseudo table. Therefore it
is referenced by a left outer join, which returns NULL for inserts. The
ISNULL then substitudes the NULL with the current value in the client table to
determine whether to update the record or not.
*/
WHERE INS.HEADER_TRANSACTIONDATE <> ISNULL(DE.HEADER_TRANSACTIONDATE,CL.CLIENT_TRANSACTIONDATE)
;
GO
/* Sample data for clients */
INSERT INTO dbo.TBL_CLIENT (CLIENT_NAME,CLIENT_TRANSACTIONDATE)
VALUES
(N'Alpha' ,'2000-01-01')
,(N'Beta' ,'2000-02-01')
,(N'Gamma' ,'2000-03-01')
,(N'Delta' ,'2000-04-01')
,(N'Epsilon','2000-05-01')
,(N'Zeta' ,'2000-06-01');
/* verify the clients */
SELECT * FROM dbo.TBL_CLIENT;
/* insert a single transactional record */
INSERT INTO dbo.TBL_HEADER (CLIENT_ID,HEADER_TRANSACTIONDATE)
SELECT
CL.CLIENT_ID
,'2001-01-01'
FROM dbo.TBL_CLIENT CL
WHERE CL.CLIENT_NAME = N'Alpha';
/* Results */
SELECT * FROM dbo.TBL_CLIENT;
/* insert multiple transactional records */
INSERT INTO dbo.TBL_HEADER (CLIENT_ID,HEADER_TRANSACTIONDATE)
SELECT
CL.CLIENT_ID
,'2002-01-02'
FROM dbo.TBL_CLIENT CL
WHERE CL.CLIENT_NAME <> N'Alpha';
/* Results */
SELECT * FROM dbo.TBL_CLIENT;
SELECT * FROM dbo.TBL_HEADER;
/* update single transactional record */
UPDATE H
SET H.HEADER_TRANSACTIONDATE = '2003-01-02'
FROM dbo.TBL_CLIENT CL
INNER JOIN dbo.TBL_HEADER H
ON CL.CLIENT_ID = H.CLIENT_ID
WHERE CL.CLIENT_NAME = N'Alpha';
/* Results */
SELECT * FROM dbo.TBL_CLIENT;
SELECT * FROM dbo.TBL_HEADER;
/* update multiple transactional records */
UPDATE H
SET H.HEADER_TRANSACTIONDATE = '2004-03-02'
FROM dbo.TBL_CLIENT CL
INNER JOIN dbo.TBL_HEADER H
ON CL.CLIENT_ID = H.CLIENT_ID
WHERE CL.CLIENT_NAME <> N'Alpha';
/* Results */
SELECT * FROM dbo.TBL_CLIENT;
SELECT * FROM dbo.TBL_HEADER;
Update: Added comments.