October 14, 2019 at 11:03 am
Hi,
I have, as most triggers are, a single row trigger; that is it will only update one row, and that works fine.
However, someone now runs a SQL Query that updates multiple rows at once, and I have not been able to see how to get this to do both.
Here is the trigger and below that is a sample of the code they are using to insert rows.
Any ideas would be greatly appreciated.
Thank you
ALTER TRIGGER [dbo].[trig_UpdatePlacementCount]
ON [dbo].[DebtorHistory]
FOR INSERT, DELETE, UPDATE
AS
BEGIN
DECLARE @ModifiedFileNo varchar(20)
-- get the inserted file_no; this will also have a
-- value if a row is updated, which involves a delete
-- and insert
IF EXISTS(SELECT 1 FROM INSERTED)
BEGIN
SELECT @ModifiedFileNo = I.file_no FROM INSERTED I
END
-- get the deleted file_no
IF EXISTS(SELECT 1 FROM DELETED)
BEGIN
SELECT @ModifiedFileNo = D.file_no FROM DELETED D
END
-- update the corresponding row in DI with
-- the new placement count
UPDATE DebtorInfo
SET [placement_count] =
(
SELECT COUNT(*)
FROM debtorhistory
WHERE agy_id NOT IN ('DMP','WARE','UP-DMP')
--AND sale_id IS NULL
AND file_no = @ModifiedFileNo
)
WHERE DebtorInfo.[file_no] = @ModifiedFileNo
END
Here is their update code:
use DMPCRU_dev
declare @agy_ID varchar (12) = 'ABC',
@date date = getdate(),
@sale_id varchar(20) = 'SD9999' ,
@sale_file varchar(50) = 'TEST 9999',
@contract_sale_price_ratio decimal (12,5) = '0.07',
@sale_buyer varchar(25) = 'ASBROCK TEST',
@misc varchar (1) = 'Q',
@NETSale_price_ratio decimal (12,5) = '0.07'
INSERT INTO DMPCRU_Dev.dbo.DebtorHistory
(file_no ,agy_ID ,agy_fee ,placement_id ,placement_balance ,placement_date ,SIF_auth ,SIF_control_no ,sale_id ,sale_file ,sale_price ,sale_buyer
,sale_date ,recalled ,recall_date, created ,createdby ,sch_recall_date ,receive_proceeds ,file_creation_date ,remit_date,
NetSale_price, brooker_price)
select file_no,
placement_count,
@agy_ID as agy_ID,
'0' as agy_fee,
'SOLD' as placement_id,
curr_prin_balance as placement_balance,
@date as placement_date,
'0' as SIF_auth,
null as SIF_control_no,
@sale_id as sale_id,
@sale_file as sale_file,
@contract_sale_price_ratio * curr_prin_balance as sale_price,
@sale_buyer as sale_buyer,
@date as sale_date,
'0' as recalled,
null as recall_date,
@date as created,
SYSTEM_USER as createdby,
null as sch_recall_date,
'1' as receive_proceeds,
null as file_creation_date,
null as remit_date ,
@NETSale_price_ratio * curr_prin_balance as NetSale_price,
(@contract_sale_price_ratio - @NETSale_price_ratio) * curr_prin_balance as brooker_price
from DMPCRU_Dev.dbo.debtorinfo
where misc_search = @misc
October 14, 2019 at 1:10 pm
I hope your assertion that most triggers are single-row triggers is incorrect, although it certainly does appear to be a mistake that a lot of developers make!
Something like this should get you started. It's not tested, obviously, so make sure it works for you before it goes anywhere near production. If the trigger is really only for updates, change the third line to FOR UPDATE
John
ALTER TRIGGER [dbo].[trig_UpdatePlacementCount]
ON [dbo].[DebtorHistory]
FOR UPDATE
AS
UPDATE di
SET placement_count = COUNT(*) OVER (PARTITION BY di.file_no)
FROM DebtorInfo di
JOIN debtorhistory dh ON di.file_no = dh.file_no
JOIN Inserted i on dh.file_no = i.file_no
WHERE dh.agy_id NOT IN ('DMP','WARE','UP-DMP');
October 14, 2019 at 2:15 pm
Thanks, I appreciate the help and I will check it out.
One last question this looks like it would also work for a single insert as well. is that right?
Thnaks
October 14, 2019 at 2:23 pm
Yes, of course. That's the intention, anyway!
John
October 14, 2019 at 2:28 pm
Something along these lines:
ALTER TRIGGER [dbo].[trig_UpdatePlacementCount]
ON [dbo].[DebtorHistory]
AFTER INSERT, UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE DI
SET placement_count = dh.file_no_count
FROM dbo.DebtorInfo DI
INNER JOIN inserted i ON i.file_no = DI.file_no
INNER JOIN (
SELECT file_no, COUNT(*) AS file_no_count
FROM dbo.debtorhistory
WHERE agy_id NOT IN ('DMP','WARE','UP-DMP')
GROUP BY file_no
) AS dh ON dh.file_no = DI.file_no
/*end of trigger*/
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 14, 2019 at 3:00 pm
Thanks again
October 14, 2019 at 3:11 pm
just make sure no-one tries to put a stored proc call inside the trigger making an assumption that it is single row only.
I've inherited triggers where they have put checks in place to roll back the transaction if more than one row is affected 🙁
even worse, to overcome putting procs in a trigger, they created a cursor that calls the proc for each row modified...it's nearly impossible to get out of a situation like that. make sure you put a big comment at the top of the trigger warning people to code for multiple rows.
🙂
MVDBA
October 14, 2019 at 4:13 pm
I would test for correct counts, but John's suggestion is a great example.
October 14, 2019 at 4:23 pm
Thanks to everyone here, especially john, it worked great and gave me a whole new perspective on triggers.
October 14, 2019 at 10:47 pm
This was removed by the editor as SPAM
October 14, 2019 at 10:49 pm
This was removed by the editor as SPAM
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply