Post reply

I'm sure the solution is simple - so I MUST be really STOOOPID or just 'SQL blind'

  • November 26, 2009 at 4:16 am

    #214182

    Hi there,

    I have two tables - one that records animal relocation exports (dbo.exports) and another that records each animal container that is a part of an export (dbo.containers) - one export event can have one or many containers.

    Here's the tables:

    CREATE TABLE dbo.containers(containerID int, shipmentID int, animalName nvarchar (25), weight decimal (18,2))

    INSERT INTO containers values (1 , 1, 'Howl', 20.00)

    INSERT INTO containers values (2 , 1, 'Meow', 22.00)

    INSERT INTO containers values (3 , 1, 'Woof', 32.00)

    INSERT INTO containers values (4 , 2, 'Bark', 40.00)

    INSERT INTO containers values (5 , 2, 'Poop', 32.00)

    INSERT INTO containers values (6 , 3, 'Ruff', 19.00)

    CREATE TABLE dbo.exports(exportID int, destination nvarchar(25), shipmentWeight decimal (18,2))

    INSERT INTO dbo.exports values (1, 'London', 0.00)

    INSERT INTO dbo.exports values (2, 'Trinidad', 0.00)

    INSERT INTO dbo.exports values (3, 'Beijing', 0.00)

    I want to calculate the TOTAL shipment weight for AN export, by adding the 'weight' value for each container in a shipment and here I have a simple query that returns the total

    weight of all containers for one export:

    select sum(weight) as totalWeight, exportID from dbo.containers where exportID = exportID GROUP BY exportID

    This returns

    totalWeightexportID

    --------------------

    741

    722

    193

    In this part of my problem - 🙂 - I need help to write insert/update triggers, that are attached to dbo.containers, that will calculate and insert the 'totalWeight' value INTO the dbo.exports.shipmentWeight column.

    Any help/suggestions/solutions HUGELY appreciated!

    Luv from Dubai

    Nick

  • November 26, 2009 at 4:24 am

    #1084537

    For a good introduction to triggers in SQL, please read this: http://www.sqlservercentral.com/articles/Triggers/64214/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply