This should do the trick:
ALTER TRIGGER [dbo].[selfbill]
ON [dbo].[tblSelfBill]
FOR UPDATE
As
BEGIN
DECLARE @Free_Text nvarchar(100);
SET NOCOUNT ON;
If Update(TotalFreeTextCommission)
SET @Free_Text= 'New Commission';
UPDATE
D
SET
Commission = I.TotalFreeTextCommission
FROM
inserted As I
INNER JOIN dbo.tblSelfBillDetail As D
ON D.SelfBillID = I.SelfBillID
WHERE
D.NoOfConnectionBF Is Null
AND
D.NoOfConnectionSold Is Null
AND
D.NoOfConnectionActivated Is Null
AND
D.NoOfConnectionCF Is Null
AND
D.NoOfConnectionReturned Is Null
AND
D.ActivationPercentage Is Null
;
INSERT INTO dbo.tblSelfBillDetail
(
SelfBillID,
AectionID,
NetworkID,
Description,
Commission,
NoOfConnectionBF,
NoOfConnectionSold,
NoOfConnectionActivated,
NoOfConnectionCF,
NoOfConnectionReturned,
ActivationPercentage
)
SELECT
I.SelfBillID,
Null,
Null,
@Free_Text,
I.TotalFreeTextCommission,
Null,
Null,
Null,
Null,
Null,
Null
FROM
inserted As I
WHERE
Not Exists
(
SELECT 1
FROM dbo.tblSelfBillDetail As D
WHERE D.SelfBillID = I.SelfBillID
AND D.NoOfConnectionBF Is Null
AND D.NoOfConnectionSold Is Null
AND D.NoOfConnectionActivated Is Null
AND D.NoOfConnectionCF Is Null
AND D.NoOfConnectionReturned Is Null
AND D.ActivationPercentage Is Null
)
;
UPDATE
I
SET
Commission = I.TotalFreeTextCommission,
Description = @Free_Text
FROM
inserted As I
INNER JOIN dbo.tblSelfBillFreeText As T
ON T.SelfBillID = I.SelfBillID
;
INSERT INTO dbo.tblSelfBillFreeText
(
SelfBillID,
Commission,
Description
)
SELECT
SelfBillID,
TotalFreeTextCommission,
@Free_Text
FROM
inserted As I
WHERE
Not Exists
(
SELECT 1
FROM dbo.tblSelfBillFreeText As T
WHERE T.SelfBillID = I.SelfBillID
)
;
END