March 26, 2004 at 9:53 am
Any suggestions for the following would be appreciated. I have tried a few different triggers but have yet to achieve the desired results.
Table: h8summ
Involved fields:
hmy: primary key
hcaseworker: FK to a caseworker table
hmasterrecd: FK (to hmy) to "master" record in h8summ table
The master record serves as a template for any new records. The problem is, the application (3rd party) is not populating hcaseworker when it inserts a new record. Hcaseworker is set to 0. Can I update this field on insert? I should be able to join inserted to h8summ on inserted.hmasterrecd = h8summ.hmy and set hcaseworker of the new record = hcaseworker of the master record. Nothing I have tried has worked.
Thank you in advance. Jill
March 26, 2004 at 10:19 am
You are correct. You should be able to do this.
create trigger tri_h8summ on h8summ for insert
as
update h
set hcaseworker = b.hcaseworker
from inserter i
inner join h8summ h
on i.pk = h.pk
inner join hmaster b
on i.fk = b.pk
return
or something similar.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
March 26, 2004 at 1:55 pm
BTW: Steve has a typo in his otherwise great code. It should be "inserted" rather than "inserter"
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
March 26, 2004 at 7:45 pm
Thank you Steve.
I wrote the following trigger:
alter trigger utr_caseworker on h8summ
for insert as
update new
set hcaseworker = mst.hcaseworker
from h8summ new
join inserted i on i.hmy = new.hmy
join h8summ mst on i.hmastersum = mst.hmy
hcaseworker would remain 0 on the inserted record. I then attached a clause to write the new.hmy and i.hmastersum values out to a little table. The other table was populating correctly. I tried the following trigger and it worked.
alter trigger utr_caseworker on h8summ
for insert as
insert aa (hsum, hcaseworker)
select i.hmy , mst.hcaseworker
from inserted i
inner join h8summ mst on i.hmastersum = mst.hmy
update h8summ
set hcaseworker = aa.hcaseworker
from h8summ
join aa on aa.hsum = h8summ.hmy
But then I get into performance issues: should I just leave the "aa" table, truncate it after the update or use a temp table instead? Is this not a recommended solution for other reasons?
Thank you. Jill
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply