Trigger to select from mult tables, only update inserted 1

[TheWkndr]

SQL2K database
I am having some difficulty with an existing trigger. I performs quite a few calculations and needs data from other tables to do so, but it only updates the one table it is attached to. It's been fine for a few years but they recently asked me to pull in one more piece of data which would have to come from a third table. The code below is the first section - i trimmed out the middle part of the declarations and first select as we're talking about 400 variables:
A is a field that exist as the same name in all tables and is the uid for the records.

IF EXISTS
(SELECT *
FROM SYSOBJECTS
WHERE ID = OBJECT_ID(N'[dbo].[Calculations]') AND OBJECTPROPERTY(ID, N'IsTrigger') = 1
)
DROP TRIGGER [dbo].[Calculations]
GO

-- 30a DEV 090918

CREATE TRIGGER [Calculations]
ON [CALLLOG]
--WITH ENCRYPTION
FOR INSERT, UPDATE
AS
SET NOCOUNT ON

DECLARE @A VARCHAR(8), @E VARCHAR(25),
@B INT, @F INT,
@C INT, @G INT,
@D INT, @H INT
-------------------------------------------------------------------------------------------------------------------------------
--Get the values for the fields

SET ROWCOUNT 1
SELECT @A = [AA], @E = [EE],
@B = [BB], @F = [FF],
@C = [CC], @G = [GG]
FROM [INSERTED]
SET ROWCOUNT 500

SET ROWCOUNT 1
SELECT
@D = [DD]
FROM SUBSET
WHERE A = @AA

-- recently added select causes trigger to not fire
SET ROWCOUNT 1
SELECT
@H = [HH]
FROM DETAIL
WHERE A = @AA


The problem started after I added this last select section. Now the trigger does not fire. If i comment out those five last lines, it works fine.
I inherited this code form someone else and we're replacing the system in a few months, so there isn't time or resources to rewite anything.

What am I doing wrong to add a table here? I appreciate the help.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TheWkndr

 

[bborissov]

NEVER, NEVER write a trigger the way you wrote it.
You are assumed that you ALWAYS will insert or update ONE record in a batch.
Remember TRIGGER fires AFTER whole batch is complete, so you may have many inserted or updated records in INSERTED or DELETED tables.
And when you have such situation you are doomed, your logic will work only for the first record (or the last, depending of WHERE clause and SET ROWCOUNT)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

Just to re-iterate (is it the right word) Boris' word:


Best Practice: Coding SQL Server triggers for multi-row operations

PluralSight Learning Library

 

[TheWkndr]

Would it make any difference if the trigger was always ONLY supposed to fire for one record at a time.
Basically, when a record is updated it fires the trigger to run through a set of calculations.

Like I said - I inherited it and we're stuck with it for a few months.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
TheWkndr

 

[bborissov]

What it is SUPPOSED to do is different from what it WILL do.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.