executing an sp within a trigger
-
What is the best way of executing a stored procedure within an AFTER INSERT trigger for every record that is inserted into the table?
Here is an illustration. I have two tables: guests and staff. A person becomes a guest first before becoming a staff. When a guest becomes a staff, I want to be able to generate a random number for that staff (which is stored in the same table).
The code below works fine if you are adding one staff at a time. However, if there are two staff records added at the same time, it will only generate a random number for the second staff record.
How do I get it to update both staff records if inserting two staff records at the same time? In other words, how do I exec the stored procedure for both newly inserted staff records?
thanks in advance,
Billy
Thanks
/* ---------- cut here ------------- */
use tempdb
begin tran
create table guest(guest_id int identity(1,1), guest_name varchar(10));
create table staff(staff_id int identity(1,1), staff_name varchar(10), staff_random_num int);
go
create proc usp_get_random_num (@staff_id int) as
begin
update staff set staff_random_num = rand() * 100 where staff_id = @staff_id;
end
go
create trigger tr_staff ON staff after INSERT AS
begin
declare @staff_id int
select @staff_id = staff_id from inserted;
exec usp_get_random_num @staff_id
end
go
SET NOCOUNT ON
insert into guest(guest_name) values('Larry');
insert into guest(guest_name) values('James');
insert into staff(staff_name) select guest_name from guest;
select * from staff
SET NOCOUNT OFF
go
rollback
/* ---------- cut here ------------- */
-
Have to use a cursor. In general you should try to do set based operations, especially in triggers. Triggers should be light weight and fast. There are times where you have to do the work though and you have an existing proc that makes sense to reuse - so using a cursor on the inserted table lets you call it for each row. The alternative is to tag each row as changed, then have a job that selects all the changed rows and processes them, then clears the flag. The latter approach keeps the work out of process - the trigger completes quickly and the work gets done later (how much later depends on the volume of course).
Andy
-
Probably not really addressing you real problem, but based on your code, why not use a computed column for the staff_random_num column?
Then you don't need proc or trigger.
Thanks
Phill Carter
--------------------
Colt 45 - the original point and click interface
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply