May 15, 2006 at 5:29 pm
what I want to accomplish is the following :
table sa_entity
org_id int
org_ver_id
< other columns not important>
PK is org_id + org_ver_id
1) add new column las_updt_reg_id
alter table sa_entity
add lst_updt_reg_id int
go
2) populate new column with prsent values
update sa_entity
set lst_updt_reg_id = dbo.function(org_id)
note : dbo.function(org_id) takes 1 argument int returns int
3) wish to continue to update column lst_updt_reg_id by trigger
create trigger ti_sa_entity on sa_entity
for insert
begin
declare @org_id int, @org_ver_id int, @last_updt_reg_id int
select @org_id = org_id from inserted
select @org_ver_id = org_ver_id from inserted
exec @last_updt_reg_id = dbo.function(@org_id)
exec sp_update_sa_entity @org_id, @org_ver_id ,@last_updt_reg_id
end
go
create procedure sp_update_sa_entity @org_id, @org_ver_id ,@last_updt_reg_id
as
begin
update sa_entity
set last_updt_reg_id = @last_updt_reg_id
where org_id = @org_id
and org_ver_id = @org_ver_id
end
go
May 15, 2006 at 11:33 pm
Remove all declarations -
declare @org_id int, @org_ver_id int, @last_updt_reg_id int
and do it set oriented.
update sa_entity
set last_updt_reg_id = dbo.function(i.org_id)
FROM inserted i
where sa_entity.org_id = i.org_id and sa_entity.org_ver_id = i.org_ver_id
That's all.
_____________
Code for TallyGenerator
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply