Adding multiple rows to a table with "insert into" 1

[Azathoth]

I have an update trigger that records to an audit table. The problem is that an update could affect multiple rows, and this code only returns the first row affected in the lab_batch table. Is there some way to insert multiple records into the aud_lab_batch table?

Here's the code:

CREATE TRIGGER aud_update_lab_batch
	ON lab_batch
    		AFTER UPDATE AS 
DECLARE
	@change_type char(1),
	@updt_userid varchar(10),
	@updt_datetime datetime,
	@batch_number char(9),
	@batch_datetime datetime,
	@entered_by varchar(10),
	@is_deleted bit,
	@delete_reason varchar(25),
	@center_id char(1)

SELECT
	@change_type = 'U',
	@batch_number = t.batch_number,
	@batch_datetime = t.batch_datetime,
	@entered_by = t.entered_by,
	@is_deleted = t.is_deleted,
	@delete_reason = t.delete_reason,
	@center_id = t.center_id,
	@updt_userid = t.updt_userid,
	@updt_datetime = t.updt_datetime
FROM
	deleted t
INSERT INTO
	aud_lab_batch
VALUES (
	@change_type,
	@batch_number,
	@batch_datetime,
	@entered_by,
	@is_deleted,
	@delete_reason,
   	@center_id,
	@updt_userid, 
	@updt_datetime )

 

[NoCoolHandle]

the key is an insert with a select...

I.E.
Insert into Northwind.dbo.Employees (firstname,lastname,title)
Select FirstName,LastName,Title from Inserted

would insert all rows from the inserted table
Important note... YOu must provide a column list to do the insert...

HTH


Rob

 

[swampBoogie]

insert into aud_lab_batch
select ... from deleted