An issue I've ran into before but never really got a handle on is how to 
set up a trigger (sql 2005) so that a column value is updated as 
appropriate for each row, when making a bulk update?

In my attempt at DDL below, the idea would be that when new data is 
updated or inserted into Table_1, the value of column MyValue would be 
copied into column MyValueCopy. If I use the inserted table to set  
MyValueCopy for a single row, MyValueCopy is updated as I'd expect. If I 
update MyValue for all rows, MyValueCopy for all rows end up with the 
MyValue value of the last record. I know there are ways to do this using 
inserted and expected it to be simple to find an example solution, but 
surprisingly I have not found anything yet.

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Table_1
	(
	MyID int NOT NULL IDENTITY (1, 1),
	MyValue varchar(50) NULL,
	MyValueCopy varchar(50) NULL
	)  ON [PRIMARY]
GO
ALTER TABLE dbo.Table_1 ADD CONSTRAINT
	PK_Table_1 PRIMARY KEY CLUSTERED 
	(
	MyID
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

INSERT INTO [mydb].[dbo].[Table_1]
           ([MyValue]
           ,[MyValueCopy])
     VALUES
           (<'Horse', varchar(50),>
           ,<NULL, varchar(50),>)
INSERT INTO [mydb].[dbo].[Table_1]
           ([MyValue]
           ,[MyValueCopy])
     VALUES
           (<'Feather', varchar(50),>
           ,<NULL, varchar(50),>)
INSERT INTO [mydb].[dbo].[Table_1]
           ([MyValue]
           ,[MyValueCopy])
     VALUES
           (<'Rake', varchar(50),>
           ,<NULL, varchar(50),>)
GO

A trigger is called once per statement, not once per row that is updated. 
So you need to write the trigger to handle multiple rows (by matching the 
rows in the inserted table with the rows in your base table on the primary 
key of the base table).  The trigger for this case could be

Create Trigger trTable_1 On dbo.Table_1 For Insert, Update As
Begin
  Update t
  Set MyValueCopy = i.MyValue
  From dbo.Table_1 t
  Inner Join inserted i On t.MyId = i.MyId;
End
GO

That will handle multiple or single row updates and inserts.

Tom

"mat" <mat@notarealdotcom.adr> wrote in message 
news:MPG.2577450e9ec0e26a989797@msnews.microsoft.com...
> An issue I've ran into before but never really got a handle on is how to
> set up a trigger (sql 2005) so that a column value is updated as
> appropriate for each row, when making a bulk update?
>
> In my attempt at DDL below, the idea would be that when new data is
> updated or inserted into Table_1, the value of column MyValue would be
> copied into column MyValueCopy. If I use the inserted table to set
> MyValueCopy for a single row, MyValueCopy is updated as I'd expect. If I
> update MyValue for all rows, MyValueCopy for all rows end up with the
> MyValue value of the last record. I know there are ways to do this using
> inserted and expected it to be simple to find an example solution, but
> surprisingly I have not found anything yet.
>
> BEGIN TRANSACTION
> GO
> CREATE TABLE dbo.Table_1
> (
> MyID int NOT NULL IDENTITY (1, 1),
> MyValue varchar(50) NULL,
> MyValueCopy varchar(50) NULL
> )  ON [PRIMARY]
> GO
> ALTER TABLE dbo.Table_1 ADD CONSTRAINT
> PK_Table_1 PRIMARY KEY CLUSTERED
> (
> MyID
> ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
> ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
>
> GO
> COMMIT
>
> INSERT INTO [mydb].[dbo].[Table_1]
>           ([MyValue]
>           ,[MyValueCopy])
>     VALUES
>           (<'Horse', varchar(50),>
>           ,<NULL, varchar(50),>)
> INSERT INTO [mydb].[dbo].[Table_1]
>           ([MyValue]
>           ,[MyValueCopy])
>     VALUES
>           (<'Feather', varchar(50),>
>           ,<NULL, varchar(50),>)
> INSERT INTO [mydb].[dbo].[Table_1]
>           ([MyValue]
>           ,[MyValueCopy])
>     VALUES
>           (<'Rake', varchar(50),>
>           ,<NULL, varchar(50),>)
> GO 

Thanks Tom, that's what I was looking for.

In article <#fi2g1hbKHA.2572@TK2MSFTNGP05.phx.gbl>, 
tomcooper@comcast.net says...
> A trigger is called once per statement, not once per row that is updated. 
> So you need to write the trigger to handle multiple rows (by matching the 
> rows in the inserted table with the rows in your base table on the primary 
> key of the base table).  The trigger for this case could be
> 
> Create Trigger trTable_1 On dbo.Table_1 For Insert, Update As
> Begin
>   Update t
>   Set MyValueCopy = i.MyValue
>   From dbo.Table_1 t
>   Inner Join inserted i On t.MyId = i.MyId;
> End
> GO
> 
> That will handle multiple or single row updates and inserts.
> 
> Tom
> 
> "mat" <mat@notarealdotcom.adr> wrote in message 
> news:MPG.2577450e9ec0e26a989797@msnews.microsoft.com...
> > An issue I've ran into before but never really got a handle on is how to
> > set up a trigger (sql 2005) so that a column value is updated as
> > appropriate for each row, when making a bulk update?
> >
> > In my attempt at DDL below, the idea would be that when new data is
> > updated or inserted into Table_1, the value of column MyValue would be
> > copied into column MyValueCopy. If I use the inserted table to set
> > MyValueCopy for a single row, MyValueCopy is updated as I'd expect. If I
> > update MyValue for all rows, MyValueCopy for all rows end up with the
> > MyValue value of the last record. I know there are ways to do this using
> > inserted and expected it to be simple to find an example solution, but
> > surprisingly I have not found anything yet.
> >
> > BEGIN TRANSACTION
> > GO
> > CREATE TABLE dbo.Table_1
> > (
> > MyID int NOT NULL IDENTITY (1, 1),
> > MyValue varchar(50) NULL,
> > MyValueCopy varchar(50) NULL
> > )  ON [PRIMARY]
> > GO
> > ALTER TABLE dbo.Table_1 ADD CONSTRAINT
> > PK_Table_1 PRIMARY KEY CLUSTERED
> > (
> > MyID
> > ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
> > ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
> >
> > GO