DROP TRIGGER delivery_charge_changes
GO
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
																				        FROM   DeliveryType, Deleted
																												WHERE  DeliveryType.DeliveryTypeID = Deleted.DeliveryTypeID
																												AND    DeliveryType.DeliveryCharge = Deleted.DeliveryCharge
																											)
																BEGIN
                                     SET @DeliveryTypeDescription = ( SELECT DeliveryType.DeliveryTypeDescription 
                                                                      FROM DeliveryType, Inserted 
                                                                      WHERE DeliveryType.DeliveryTypeID = Inserted.DeliveryTypeID
                                                                    )
                                     SET @OldDeliveryCharge       = ( SELECT Deleted .DeliveryCharge 
                                                                      FROM DeliveryType, Deleted 
                                                                      WHERE DeliveryType.DeliveryTypeID = Deleted.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
												-- END IF
								END

RETURN
GO