update a field being inserted by another record in the same table

[Vack58]

Using trigger
want to update a field being inserted from another record in the same table.

the record being inserted I want to pull the bkjrcode from another record where the account = 1040 that also has the same ord# and inv# as the record being inserted.

Here is what I've tried with no luck.

create trigger [updategbkmut] on [dbo].[gbkmut]
after insert
as

update g1
set g1.bkjrcode = g2.bkjrcode
from gbkmut g1
inner join inserted i
on i.ord_no = g1.ord_no and i.inv_no = g1.inv_no
inner join gbkmut g2
on i.ord_no = g2.ord_no and i.inv_no = g2.inv_no
where i.freefield3 = 'Rebate' and g1.account = '1040'

 

[SQLSister]

Not sure if this is your problem, but in an after trigger, the record you inserted is already inthe table. I suspect you are updating the record with the values in it's own record (which are probably null) instead of the other record. When I join to grab a differnt record from the same table I try to include something in the where clause or join that will distinguish the two records from each other. For instance you might add i.[youridfield] <> g2.[youridfield] to the join or where clasue and see if that fixes the problem. Or you might try g2.bkjrcode is not null

"NOTHING is more important in a database than integrity." ESquared

 

[bborissov]

For what is worth to use trigger for this?
Why not use correct INSERT statement?
What if you INSERT 2000,10000,100000(0) at a time?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Vack58]

How would the insert work? Looks like I would still run into the same problem wanting to update a field in the inserted record during the insert.

 

[bborissov]

Could you post your INSERT statement and what you want as a result?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Vack58]

Here is a sample of the table I'm inserting into.

Table gbkmut

bkjrcode reknr datum ord_no inv_no
2008 1040 05/29/2008 39 246
2008 2030 05/29/2008 39 246

Above are the existing records I want to pull data from.

I'm inserting data from another table but only bringing over a couple of fields. So i need to update all other fields with data from existing records. That is why I thought a trigger would work. After I insert from the other table I would update the other fields from the existing data.

Here is my insert statement where I'm inserting from the orderrebatehistory table:
insert into gbkmut(
bdr_hfl,
btw_bdr_3,
btw_grond,
aantal,
koers,
wisselkrs,
koers3,
bdr_val,
dbk_verwnr)
select
orderrebatehistory.ext_rebate,
orderrebatehistory.btw_bdr_3,
orderrebatehistory.btw_grond,
orderrebatehistory.aantal,
orderrebatehistory.koers,
orderrebatehistory.wisselkrs,
orderrebatehistory.koers3,
orderrebatehistory.ext_rebate,
orderrebatehistory.dbk_verwnr
FROM orderrebatehistory
where OrderRebateHistory.Posted_fg = 'N'


After the insert above is done, I need to update the other fields in the gbkmut table from one of the existing records where the ord_no and Inv_no match.

Hope this helps

 

[bborissov]

Then use:

insert into gbkmut(bdr_hfl,
                   btw_bdr_3,
                   btw_grond,
                   aantal,
                   koers,
                   wisselkrs,
                   koers3,
                   bdr_val,
                   dbk_verwnr
                   [Field list that should be updated from other table)
select orderrebatehistory.ext_rebate,
       orderrebatehistory.btw_bdr_3,
       orderrebatehistory.btw_grond,
       orderrebatehistory.aantal,
       orderrebatehistory.koers,
       orderrebatehistory.wisselkrs,
       orderrebatehistory.koers3,
       orderrebatehistory.ext_rebate,
       orderrebatehistory.dbk_verwnr,
       [Field List from Othertable]
FROM orderrebatehistory
INNER JOIN OtherTable ON
       orderrebatehistory.ord_no = OtherTable.ord_no
       orderrebatehistory.Inv_no = OtherTable.Inv_no
where OrderRebateHistory.Posted_fg = 'N'

Just make your SELECT to get the values you want.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Vack58]

That doesn't quite work. It is inserting too many rows.
There are two rows in the orderrebatehistory that I need to move to the gbkmut.

The GBkmut already has 5 existing records in it. When I run the above it insertes 10 records instead of 2.

 

[bborissov]

Vack58,
As I said just make your SELECT to get records you need.
How about:

select DISTINCT orderrebatehistory.ext_rebate,
       orderrebatehistory.btw_bdr_3,
       orderrebatehistory.btw_grond,
       orderrebatehistory.aantal,
       orderrebatehistory.koers,
       orderrebatehistory.wisselkrs,
       orderrebatehistory.koers3,
       orderrebatehistory.ext_rebate,
       orderrebatehistory.dbk_verwnr,
       [Field List from Othertable]
FROM orderrebatehistory
INNER JOIN OtherTable ON
       orderrebatehistory.ord_no = OtherTable.ord_no
       orderrebatehistory.Inv_no = OtherTable.Inv_no
where OrderRebateHistory.Posted_fg = 'N'

Keep in mind that I DON'T know your tables and database. I'm just guessing here.
If you want post some example records from BOTH tables and what you want to be inserted in gbkmut table.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Vack58]

gbkmut is the table I want to insert into
orderrebatehistory is the table i'm pulling data from.


When records get written to the orderrebatehistory I need to insert the same number of records into the gbkmut table.

The only two fields that link the two tables together are:
gbkmut.bstnr_sub = orderrebatehistory.ord_no
gbkmut.faktuurnr = orderrebatehistory.inv_no

The orderrebatehistory has some of the same fields the gbkmut table has and I have no problems getting those inserted.

The problem comes when I try to populate the rest of the fields in the gbkmut table. I have to read the existing records in the gbkmut table that have the same ord_no and inv_no as the inserted records in order to know what to populate in the existing fields.

For example I have two records in the Orderrebatehistory.
Orderrebatehistory table
Ord_no Inv_no Rebate_amt
29 408 123.25
29 408 12.22

gbkmut table
Ord_no Inv_no amt date account cus
29 408 400.00 5/29/2008 1040 901
29 408 35.00 5/29/2008 4444 901
29 408 78.25 5/29/2008 5328 901

I need to take the two records in the order rebate table and insert them into the gbkmut table but will also need to populate the date and account and cus fields from one of the records in the gbkmut table.

Thanks for all your help so far.

 

[bborissov]

OK, that is not so difficult (maybe) IF we have whole information.
Please, PLEASE provide WHOLE information what do you want.
What is that Rebate table?
I didn't see it in you example.
You have these records in your tables:
[tt]
Orderrebatehistory table
Ord_no Inv_no Rebate_amt
29 408 123.25
29 408 12.22

gbkmut table
Ord_no Inv_no amt date account cus
29 408 400.00 5/29/2008 1040 901
29 408 35.00 5/29/2008 4444 901
29 408 78.25 5/29/2008 5328 901
[/tt]

Where is Rebate table?
What you want to be inserted in gbkmut finally?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Vack58]

actually it looks like the Select Distinct did the trick.

But now I have another issue.

with the rebate_amt I'm inserting I need to sum those and subtract from another amt in the gbkmut table.

ord_no inv_no amt Account freefield3
25 335 100.00 1444
25 335 10.00 3500 Rebate
25 335 10.00 3500 Rebate

Where freefield3 = rebate i need to sum the amt field and then subtract from the amt where account = 1444 where ord no and inv no equal.