85 lines
2.1 KiB
Transact-SQL
85 lines
2.1 KiB
Transact-SQL
CREATE TRIGGER [dbo].[TRG_231_01_CLFLINE_XTS]
|
|
ON [dbo].[LG_231_01_CLFLINE]
|
|
AFTER INSERT, UPDATE, DELETE
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
DECLARE @ClientId INT
|
|
DECLARE @Cancelled INT
|
|
DECLARE @XTCount INT
|
|
|
|
DECLARE cur CURSOR LOCAL FOR
|
|
|
|
SELECT DISTINCT CLIENTREF FROM(
|
|
SELECT CLIENTREF FROM INSERTED
|
|
UNION ALL
|
|
SELECT CLIENTREF FROM DELETED
|
|
) AS X
|
|
|
|
OPEN cur
|
|
FETCH NEXT FROM cur INTO @ClientId
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SELECT @XTCount = COUNT(LOGREF) FROM LG_XT1015_231 WHERE PARLOGREF = @ClientId
|
|
|
|
DECLARE
|
|
@BalanceGBP AS FLOAT = 0,
|
|
@BalanceTL AS FLOAT = 0,
|
|
@BalanceUSD AS FLOAT = 0,
|
|
@BalanceEUR AS FLOAT = 0
|
|
|
|
SELECT
|
|
@BalanceGBP = @BalanceGBP + (CASE WHEN TRCURR=17 THEN (CASE WHEN SIGN = 1 THEN -1*SUM(TRNET) ELSE SUM(TRNET) END) ELSE 0 END),
|
|
@BalanceTL = @BalanceTL + (CASE WHEN TRCURR IN (0,160) THEN (CASE WHEN SIGN = 1 THEN -1*SUM(CASE WHEN TRRATE=0 THEN AMOUNT ELSE TRNET END) ELSE SUM(CASE WHEN TRRATE=0 THEN AMOUNT ELSE TRNET END) END) ELSE 0 END),
|
|
@BalanceUSD = @BalanceUSD + (CASE WHEN TRCURR=1 THEN (CASE WHEN SIGN = 1 THEN -1*SUM(TRNET) ELSE SUM(TRNET) END) ELSE 0 END),
|
|
@BalanceEUR = @BalanceEUR + (CASE WHEN TRCURR=20 THEN (CASE WHEN SIGN = 1 THEN -1*SUM(TRNET) ELSE SUM(TRNET) END) ELSE 0 END)
|
|
FROM LG_231_01_CLFLINE
|
|
WHERE
|
|
CLIENTREF = @ClientId AND CANCELLED=0
|
|
GROUP BY
|
|
CLIENTREF,
|
|
TRCURR,
|
|
SIGN
|
|
|
|
IF(@XTCount > 0)
|
|
BEGIN
|
|
|
|
UPDATE LG_XT1015_231 SET
|
|
GBPBAKIYE = CAST (@BalanceGBP AS DECIMAL(18,2)),
|
|
TLBAKIYE = CAST (@BalanceTL AS DECIMAL(18,2)),
|
|
USDBAKIYE = CAST (@BalanceUSD AS DECIMAL(18,2)),
|
|
EURBAKIYE = CAST (@BalanceEUR AS DECIMAL(18,2))
|
|
WHERE PARLOGREF = @ClientId
|
|
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
|
|
INSERT INTO LG_XT1015_231 (PARLOGREF,GBPBAKIYE,TLBAKIYE,USDBAKIYE,EURBAKIYE)
|
|
VALUES
|
|
(
|
|
@ClientId,
|
|
CAST (@BalanceGBP AS DECIMAL(18,2)),
|
|
CAST (@BalanceTL AS DECIMAL(18,2)),
|
|
CAST (@BalanceUSD AS DECIMAL(18,2)),
|
|
CAST (@BalanceEUR AS DECIMAL(18,2))
|
|
)
|
|
|
|
END
|
|
|
|
FETCH NEXT FROM cur INTO @ClientId
|
|
END
|
|
|
|
CLOSE cur
|
|
DEALLOCATE cur
|
|
END;
|
|
|
|
|
|
|
|
|
|
GO
|
|
|
|
|