SQL Server 2005 Update Trigger Effect Multiple Rows

• Posted: August 15, 2008
• |
• Author: Vorleak Chy
• |
• Filed under: SQL Server
• |
• Tags: SQL Server, T-Sql

I have two tables which I would like to keep records were changed in DeliveryTypeChanges if user updates the DeliveryCharge in the DeliveryType table.

First time I try to create a trigger that the “SELECT” statement without having “INNER JOIN Deleted” and keep value from “Deleted” to variable before “INSERT”, So what I get is only one record updates to the table.
First time I try to create a trigger that the “SELECT” statement without having “INNER JOIN Deleted” and keep value from “Deleted” to variable before “INSERT”, So what I get is only one record updates to the table.

There are two tables:

Create Table DeliveryType (
    DeliveryTypeID int not null
    Constraint pk_DeliveryType_DeliveryTypeID primary key,
    DeliveryTypeDescription varchar(10) not null,
    DeliveryCharge smallmoney not null
)

Create Table DeliveryTypeChanges (
    ChangeID int identity(1,1) not null
    Constraint pk_DeliveryTypeChanges_ChangeID primary key,
    ChangeDateTime datetime not null,
    DeliveryTypeDescription varchar(10) not null,
    OldDeliveryCharge smallmoney not null,
    NewDeliveryCharge smallmoney not null
)

Here is the trigger to make it works with multiple records:

CREATE TRIGGER trUpdateRecordDeliveryChargeChange
On DeliveryType
FOR UPDATE AS
IF UPDATE (DeliveryCharge)
BEGIN 

    INSERT DeliveryTypeChanges(ChangeDateTime, DeliveryTypeDescription, OldDeliveryCharge, NewDeliveryCharge)
    SELECT GETDATE(), Inserted.DeliveryTypeDescription, Deleted.DeliveryCharge, Inserted.DeliveryCharge
    FROM Inserted INNER JOIN Deleted
    ON Inserted.DeliveryTypeID = Deleted.DeliveryTypeID

END

• (2) Comments
• Permalink
• RSS 2.0