February 19, 2022 at 4:46 pm
Hello comunity,
recently I needed to create a trigger in SQL for Insert, update and delete and I used a subquery to do the sum of the amounts.
Trigger works very well but I would like your opinion on whether it can be optimized or not?
My question is when will I be able to have many rows in my BI table if the recording can become slow.
this is my trigger code:
ALTER TRIGGER trgUpdTblu_Tamanhos_USRQTT_RESCLI
ON BI
AFTER insert, delete, UPDATE
AS
DECLARE @rescli BIT
DECLARE @qtt NUMERIC(10,2)
SELECT @rescli = rescli, @qtt = qtt FROM INSERTED
IF EXISTS (SELECT 1 FROM inserted)
IF @rescli = 0
BEGIN
UPDATE Art
SET usrqtt = (SELECT SUM(qtt - qtt2) FROM BI
WHERE LTRIM(RTRIM(ref)) = LTRIM(RTRIM(i.ref)) AND LTRIM(RTRIM(litem2)) = LTRIM(RTRIM(i.litem2)) AND LTRIM(RTRIM(lobs2))= LTRIM(RTRIM(i.lobs2))
AND bi.rescli = @rescli AND i.resusr= 1 AND bi.bostamp = i.bostamp
GROUP BY LTRIM(RTRIM(bi.ref)), LTRIM(RTRIM(bi.lobs2)) , LTRIM(RTRIM(bi.litem2)))
FROM inserted i INNER JOIN bi ON bi.bostamp = i.bostamp
INNER JOIN bo2 ON i.bostamp = bo2.bo2stamp
INNER JOIN u_tamarm4 Art
ON ( LTRIM(RTRIM(Art.artigo)) = LTRIM(RTRIM(i.ref)) AND LTRIM(RTRIM(Art.corphc)) = LTRIM(RTRIM(i.lobs2)) AND LTRIM(RTRIM(Art.tam)) = LTRIM(RTRIM(i.litem2)) )
INNER JOIN ts ON ts.ndos = i.ndos
WHERE ( i.armazem = 4 OR i.ar2mazem = 4 ) AND i.resusr = 1 AND i.fechada = 0 AND bo2.anulado = 0 AND ts.rescli = @rescli
END
ELSE
BEGIN
SET @rescli = 1
UPDATE Art
SET qttcli = ISNULL( (SELECT SUM(qtt - qtt2) FROM BI
WHERE LTRIM(RTRIM(ref)) = LTRIM(RTRIM(i.ref)) AND LTRIM(RTRIM(litem2)) = LTRIM(RTRIM(i.litem2)) AND LTRIM(RTRIM(lobs2))= LTRIM(RTRIM(i.lobs2))
AND bi.rescli = @rescli AND i.resusr= 0 AND bi.bostamp = i.bostamp
GROUP BY LTRIM(RTRIM(bi.ref)), LTRIM(RTRIM(bi.lobs2)) , LTRIM(RTRIM(bi.litem2))), 0)
FROM inserted i INNER JOIN bi ON bi.bostamp = i.bostamp
INNER JOIN bo2 ON i.bostamp = bo2.bo2stamp
INNER JOIN u_tamarm4 Art
ON ( LTRIM(RTRIM(Art.artigo)) = LTRIM(RTRIM(i.ref)) AND LTRIM(RTRIM(Art.corphc)) = LTRIM(RTRIM(i.lobs2)) AND LTRIM(RTRIM(Art.tam)) = LTRIM(RTRIM(i.litem2)) )
INNER JOIN ts ON ts.ndos = i.ndos
WHERE ( i.armazem = 4 OR i.ar2mazem = 4 ) AND i.resusr = 0 AND i.fechada = 0 AND bo2.anulado = 0 AND ts.rescli = @rescli
END
ELSE IF EXISTS (SELECT 1 FROM deleted)
IF @rescli = 0
BEGIN
UPDATE Art
SET usrqtt = ISNULL( (SELECT SUM(qtt - qtt2) FROM BI
WHERE
LTRIM(RTRIM(ref)) = LTRIM(RTRIM(d.ref)) AND LTRIM(RTRIM(litem2)) = LTRIM(RTRIM(d.litem2)) AND LTRIM(RTRIM(lobs2))= LTRIM(RTRIM(d.lobs2))
AND bi.rescli = @rescli AND d.resusr= 1 AND bi.bostamp = d.bostamp
GROUP BY LTRIM(RTRIM(bi.ref)), LTRIM(RTRIM(bi.lobs2)) , LTRIM(RTRIM(bi.litem2))),0)
FROM deleted d
INNER JOIN bo2 ON d.bostamp = bo2.bo2stamp
INNER JOIN u_tamarm4 Art
ON ( LTRIM(RTRIM(Art.artigo)) = LTRIM(RTRIM(d.ref)) AND LTRIM(RTRIM(Art.corphc)) = LTRIM(RTRIM(d.lobs2)) AND LTRIM(RTRIM(Art.tam)) = LTRIM(RTRIM(d.litem2)) )
INNER JOIN ts ON ts.ndos = d.ndos
WHERE ( d.armazem = 4 OR d.ar2mazem = 4 ) AND d.resusr = 1 AND d.fechada = 0 AND bo2.anulado = 0 AND ts.rescli = @rescli
END
ELSE
BEGIN
SET @rescli = 1
UPDATE Art
SET qttcli = ISNULL( (SELECT SUM(qtt - qtt2) FROM BI
WHERE
LTRIM(RTRIM(ref)) = LTRIM(RTRIM(d.ref)) AND LTRIM(RTRIM(litem2)) = LTRIM(RTRIM(d.litem2)) AND LTRIM(RTRIM(lobs2))= LTRIM(RTRIM(d.lobs2))
AND bi.rescli = @rescli AND d.resusr= 0 AND bi.bostamp = d.bostamp
GROUP BY LTRIM(RTRIM(bi.ref)), LTRIM(RTRIM(bi.lobs2)) , LTRIM(RTRIM(bi.litem2))),0)
FROM deleted d
INNER JOIN bo2 ON d.bostamp = bo2.bo2stamp
INNER JOIN u_tamarm4 Art
ON ( LTRIM(RTRIM(Art.artigo)) = LTRIM(RTRIM(d.ref)) AND LTRIM(RTRIM(Art.corphc)) = LTRIM(RTRIM(d.lobs2)) AND LTRIM(RTRIM(Art.tam)) = LTRIM(RTRIM(d.litem2)) )
INNER JOIN ts ON ts.ndos = d.ndos
WHERE ( d.armazem = 4 OR d.ar2mazem = 4 ) AND d.resusr = 0 AND d.fechada = 0 AND bo2.anulado = 0 AND ts.rescli = @rescli
END
GO
Thank you in advance for all your wise comments.
Best regards,
Luis
February 20, 2022 at 12:48 pm
SELECT @rescli = rescli, @qtt = qtt FROM INSERTED
This suggests the trigger does not really work. Triggers need to allow for multiple rows being inserted/updated. At the very least assign variables like the following so an exception is thrown if there are multiple rows:
SET @rescli = (SELECT rescli FROM inserted);
SET @qtt = (SELECT qtt FROM inserted);
As for the rest it is difficult to tell without DLL and test data. All the LTRIM(RTRIM is not going to help but maybe you have to cope with poor quality data. If the trigger is really slow you could also look at off loading the the relevant values to a queue and doing the updates asynchronously.
February 20, 2022 at 1:56 pm
Imo three triggers would be preferred. Right from the start it's confusing because it's a "catch all" for the 3 scenarios, INSERT, DELETE, UPDATE. In code each scenario is intended to be treated separately. It appears (without a test set up) the last BEGIN/END code block is "unreachable" by the flow of control because any UPDATE will populate rows in both the INSERTED and DELETED virtual tables. So if there's an UPDATE the first (of the two) code blocks will execute and the 2nd will never execute. Apparently (again not tested) the 2 blocks do the same thing. In the JOIN and WHERE clause(s) on the left and righthand side of equivalencies there are LTRIM/RTRIM functions. For performance etc. it's preferred to get rid of that. It's unclear if/how to refactor the queries. Why it aggregates BI in a subquery when it JOINs to BI in the FROM clause of the UPDATE?
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply