T-SQL: Set Value of Variable based on subquery result in a Update Trigger

Question

I'm having difficulty figuring out how to do this.  I'm trying to use an Update Trigger to write cumulative values from all records in two fields in the udEEOTrackingHours table to another field in the update record in the same table. 

I want the value of udEEOTrackingHours.TDEmployeeHours (in the current update record) = udEEOTracking.TotalEmp_M + udEEOTracking.TotalEmp_F (cumulative from all records including the record subject to the Update Trigger). 

I'm setting the value of the TDEmployeeHOurs to the sum value of two subqueries.

Should I be using a variable for this?

Thanks,

...bob

HERE IS MY CODE:

CREATE TRIGGER [dbo.[udtrgEEOTrackingHours_ControlTotals_UPDATE]

AFTER UPDATE AS

SET NOCOUNT ON;

DECLARE @TDEmployHours INT

BEGIN
   UPDATE budEEOTrackingHours
   
   --SET TO-DATE EMPLOYEE HOURS
   SET TDEmployeeHours =
    (SELECT
    SUM(TotalEmp_M)
    FROM budEEOTrackingHours
    WHERE
     Co = inserted.Co        
     AND JobNum = inserted.JobNum        
     AND SLID = inserted.SLID        
     AND VendorNum = inserted.VendorNum) 
     
    +        
    
    (SELECT
    SUM(TotalEmp_F)
    FROM budEEOTrackingHours
    WHERE
     Co = inserted.Co        
     AND JobNum = inserted.JobNum        
     AND SLID = inserted.SLID        
     AND VendorNum = inserted.VendorNum)

END

GO

---

Bob Sutor

Answer 1

The short answer is no - you don't appear to need a variable.

It helps to post valid tsql - your trigger will not compile since you left out the table for which the trigger is defined.  The name of the trigger is also not valid due to the misaligned brackets. Let's move past that and assume a valid script.  You've made a common mistake about triggers - a trigger executes at the statement level and not on a row-by-row level.  When the trigger logic executes, there could be zero, 1, or many rows in the inserted and deleted tables. You need to write your code to correctly handle multiple rows.

Second, you did not post DDL of the table involved.  So we have no idea of what the primary key is.  Without that knowledge, it is not possible to know if your queries are logically correct. In addition, you refer to columns in the inserted table, yet you do not include the inserted table in any from clause of your queries.  Obviously, that will not work.  The inserted and deleted tables must be used in the same manner as any other table; they don't automatically get included in any sql statements in the trigger.

Third, I'm not certain exactly what you are trying to accomplish since you seem to suggest that there is one row within the table (on some undefined basis) that has a total based on a number of related rows within the same table.  That sounds like a logically flawed design, but perhaps I misunderstood.  If all you really need is a column within the table to hold the value (TotalEmp_M + TotalEmp_F) for each row, that could easily be done with a computed column.  Somehow I don't think that is what you are after and we go back to the flawed design.  It would help to see some sample data, a sample update statement, and the desired result. 

Answer 2

>> I'm trying to use an Update Trigger to write cumulative values from all records [sic] in two fields [sic] in the udEEO_Tracking_Hours table to another field [sic] in the update record [sic] in the same table. <<

Rows are not records; columns are not fields; the goal of a database is to removed redundancy, not increase it. It is also very rude not to post DDL. TRIGGERs are seldom used in good Schema designs; we want to use declarative code and not mimic 1950's punch card system in SQL. 

>> I want the value of udEEOTrackingHours.TD_EmployeeHours (in the current [sic: most recent?] update record [sic]) = udEEOTracking.TotalEmp_M + udEEOTracking.TotalEmp_F (cumulative from all records [sic] including the record [sic] subject to the Update Trigger). <<

Did you know that in T-SQL, a TRIGGER is done on the table as a whole? There is no “current row” being updated. There is no temporal value of any kind in your your non-working code. Other SQLs have that feature so that bad programmers can keep doing record processing; you can kludge it with a huge overhead. 

My guess, since what you posted is no help, is that you could use a VIEW that computes the hours for each date based on a table of timescards. We need raw data from some place. 

CREATE VIEW Running_Total_Job_Hrs
AS
SELECT posting_date, job_nbr, sl_id, vendor_nbr, 
       SUM (emp_hours) 
       OVER (PARTITION BY job_nbr, sl_id, vendor_nbr
              ORDER BY posting_date
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS emp_hours_run_tot
  FROM Something_Timecards;

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL