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

  • 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





    • Edited by ConstPM Thursday, July 4, 2013 8:59 PM
    Thursday, July 4, 2013 7:14 PM

Answers

  • 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. 

    • Marked as answer by Kalman Toth Friday, July 12, 2013 1:20 PM
    Friday, July 5, 2013 2:39 PM

All replies

  • 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. 

    • Marked as answer by Kalman Toth Friday, July 12, 2013 1:20 PM
    Friday, July 5, 2013 2:39 PM
  • >> 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

    Friday, July 5, 2013 3:59 PM