CREATE TRIGGER delivery_charge_changes
on DeliveryType
FOR UPDATE
AS
        DECLARE @RowCount int,
		@Error int,
                @DeliveryTypeDescription varchar(10),
                @OldDeliveryCharge smallmoney,
                @NewDeliveryCharge smallmoney
	IF @@RowCount > 0 AND UPDATE(DeliveryCharge)
		BEGIN
			IF NOT Exists ( SELECT DeliveryType.DeliveryTypeID add a change
                                        FROM DeliveryType, Inserted
                                        WHERE  DeliveryType.DeliveryCharge = Inserted.DeliveryCharge
                                        AND    DeliveryType.DeliveryTypeID = Inserted.DeliveryTypeID
                                      )
                                    
                                     SET @DeliveryTypeDescription = ( SELECT DeliveryType.DeliveryTypeDescription 
                                                                      FROM DeliveryType, Inserted 
                                                                      WHERE DeliveryType.DeliveryTypeID = Inserted.DeliveryTypeID
                                                                    )
                                     SET @OldDeliveryCharge       = ( SELECT DeliveryType.DeliveryCharge 
                                                                      FROM DeliveryType, Inserted 
                                                                      WHERE DeliveryType.DeliveryTypeID = Inserted.DeliveryTypeID
                                                                    )
                                     SET @NewDeliveryCharge       = ( SELECT Inserted.DeliveryCharge 
                                                                      FROM DeliveryType, Inserted
                                                                      WHERE DeliveryType.DeliveryTypeID = Inserted.DeliveryTypeID
                                                                    )

                                     INSERT INTO DeliveryTypeChanges (                                                                       ChangeDateTime, 
                                                                       DeliveryTypeDescription, 
                                                                       OldDeliveryCharge, 
                                                                       NewDeliveryCharge
                                                                     )
                                     VALUES ( getDate(), 
                                              @DeliveryTypeDescription,
                                              @OldDeliveryCharge,
                                              @NewDeliveryCharge
                                            )

                                     SELECT @Error = @@Error, @RowCount = @@Rowcount
                                     IF @Error <> 0
                                            BEGIN
                                                   ROLLBACK TRANSACTION										                                           RAISERROR('There was an error in the insert transaction cancelled', 16, 1)
                                            END
                                     ELSE
                                            BEGIN
					           IF @RowCount = 0
                                                          BEGIN
                                                                 ROLLBACK TRANSACTION
                                                                 RAISERROR('0 Rows Affected insert cancelled, 16, 1)
                                                          END
                                                   -- END IF
				            END
                                     -- END IF
                       -- END IF
		END
RETURN
GO