CREATE TRIGGER trg_update_ratedtickets_cost_charge 
ON dbo.DispatchRatedTickets 
FOR UPDATE, INSERT
AS
BEGIN 
	DECLARE @thisRatingID as int
	DECLARE @thisChargeQuantity as int
	DECLARE @thisTicketID as int
	DECLARE @thisChargeID as int
	DECLARE @thisManifestID as int
	DECLARE @thisDriverID as int
	DECLARE @thisCompanyID as int
	DECLARE @thisPostalCode as varchar
	DECLARE @thisTotalWeight as int
	DECLARE @thisChargeRate as float
	DECLARE @thisChargeMin as int	
	DECLARE @thisFreightCharge as money
	DECLARE @thisFreightCost as money
	DECLARE @newAmount as money
	DECLARE @newCost as money
	DECLARE @newchargeAmount as money
	DECLARE @newChargeCost as money
	
	SET @thisRatingID =  (SELECT ratingID FROM DispatchRatedTickets WHERE ratingID IN (SELECT ratingID FROM inserted))
	SET @thisChargeQuantity = (SELECT chargeQuantity FROM DispatchRatedTickets WHERE ratingID = @thisRatingID)
	SET @thisTicketID = (SELECT ticketID FROM DispatchRatedTickets WHERE ratingID = @thisRatingID)	
	SET @thisChargeID = (SELECT chargeID FROM DispatchRatedTickets WHERE ratingID = @thisRatingID)
	SET @thisManifestID = (SELECT DISTINCT manifestID FROM DispatchManifestTickets WHERE ticketID = @thisTicketID)
	SET @thisDriverID = (SELECT driverID FROM DispatchManifests WHERE manifestID = @thisManifestID)
	SET @thisCompanyID = (SELECT companyID FROM DispatchDrivers WHERE driverID = @thisDriverID)
	SET @newCost = (SELECT chargeCost FROM DispatchCosts WHERE companyID = @thisCompanyID AND chargeID = @thisChargeID)
	SET @newAmount = (SELECT chargeAmount FROM DispatchCharges WHERE chargeID = @thisChargeID)
	
	-- Set Ticket Destination PostalCode & Weight
	Set @thisPostalCode = (SELECT postalCode FROM DispatchTickets WHERE ticketID = @thisTicketID)
	Set @thisTotalWeight = (SELECT totalWeight FROM DispatchTickets WHERE ticketID = @thisTicketID)
	
	-- Set the rate for the code based on the total weight of the shipment
	Set @thisChargeRate = (SELECT chargeRate FROM DispatchRates WHERE postalCode = @thisPostalCode AND @thisTotalWeight >= chargeStart AND @thisTotalWeight <= chargeStop)
	Set @thisChargeMin = (SELECT chargeMin FROM DispatchRates WHERE postalCode = @thisPostalCode AND @thisTotalWeight >= chargeStart AND @thisTotalWeight <= chargeStop)
			
	-- Calculate freight charge
	SET @thisFreightCharge = (@thisTotalWeight * @thisChargeRate / 100)		
	
	-- Compare to minimum charge & adjust if needed
	IF (@thisFreightCharge < @thisChargeMin) BEGIN
		SET @thisFreightCharge = @thisChargeMin
	END			
	
	-- Calculate freight cost
	IF (@thisCompanyID <> 1) BEGIN
		IF (@thisTotalWeight < 954) BEGIN
			SET @thisFreightCost = (@thisTotalWeight * 0.0516)
		END
		ELSE IF (@thisTotalWeight >= 954 AND @thisTotalWeight <= 1934) BEGIN
			SET @thisFreightCost = (@thisTotalWeight * 0.0492)
		END
		ELSE IF (@thisTotalWeight >= 1935 AND @thisTotalWeight <= 3465) BEGIN
			SET @thisFreightCost = (@thisTotalWeight * 0.0476)
		END
		ELSE IF (@thisTotalWeight >= 3466) BEGIN
			SET @thisFreightCost = (@thisTotalWeight * 0.0330)
		END
		IF (@thisFreightCost > 9.69) BEGIN
			SET @thisFreightCost = 9.69
		END
	END
	ELSE BEGIN
		SET @thisFreightCost = (@thisFreightCharge * 0.80)
	END
	
	IF (@thisChargeID = 0) BEGIN
		Set @newChargeAmount = @thisFreightCharge
		Set @newChargeCost = @thisFreightCost
	END
	ELSE IF (@thisChargeID = 15) BEGIN
		Set @newChargeAmount = @newAmount * 0.05
		Set @newChargeCost = 0
	END
	ELSE BEGIN
		Set @newChargeAmount = @newAmount * @thisChargeQuantity
		Set @newChargeCost = @newCost * @thisChargeQuantity
	END

	UPDATE DispatchRatedTickets
	SET 	chargeAmount = @newChargeAmount
		,chargeCost = @newChargeCost
	WHERE ratingID = @thisRatingID

END

quote:

---

IF (COLUMNS_UPDATED())

Tests to see, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns of the table were inserted or updated.

COLUMNS_UPDATED can be used anywhere inside the body of the trigger.

---

quote:

---

I realize the triggers fire in batches and fully understand the way that the virtual tables of INSERTED and DELETED are used. However, I need to find a value within the "current" row that is being updated/inserted

---

quote:

---

Originally posted by tuenty

http://www.databasejournal.com/features/mssql/article.php/1479821

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*
A candle loses nothing by lighting another candle

---