Michelle, since you are using a trigger, I would submit that most inserts using the values clause are a BAD idea in a trigger.
Why? Because a trigger processes data in sets. If the insert was of 20000 records, your trigger must handle all 20000 not just the last one (or is it the first one that gets populated, I forget). Trigger have two psuedo tables that are only available in the trigger called inserted and deleted. It is better to use those in a select clause instead. Sp for instance suppose you want to create a record in a child table whenever your trigger kicks off.
Chrissie, I've never used the nsert command, what does it do?
You will have a problem if multiple records are inserted if you do something like:
Code:
declare @field1 int
select @field1 = idfield from inserted
insert table2 (field1, insertdate)
values(@field1, getdate())
You would want to do something like this instead
Code:
insert table2 (field1, insertdate)
select idfield, getdate() from inserted
This would insert all 20000 new records. This is just a simplified example, but hopefully it gives you the idea. The third thign you could do is something like the top stament but inside a cursor. This would be an extremely poor idea. It would work but if someone does a large insert it would be very slow and would probably create locking issues on the database while it runs. I just worked to fix a trigger that someone used a cursor in and changed the time to run for 40000 records from 47 minutes to 32 seconds, so you can see that cursors are in genral a bad idea especially in a trigger.
"NOTHING is more important in a database than integrity." ESquared
