Simple Trigger Query

[rafeman]

Hi,

I have the following trigger set up. All its designed to do is after an insert into the PL_TRANSACTION table it copys data from the PT_BATCH_REF field into PT_INTERNAL_REF. However my issue is at present it updates all rows with this information. I want to to only effect the inserted row. I thought the 'from inserted' did this but apparently not. I'm sure this is simple. Thanks

ALTER trigger [dbo].[Internal_Update]
on [dbo].[PL_TRANSACTIONS]
after insert
as
declare
@internalref float

select
@internalref = cast(pt_batch_ref as float)

from inserted

update pl_transactions
set pt_internal_ref = @internalref

 

[gmmastros]

There are a couple problems with your trigger. The biggest problem is that it will only ever update one row. If you insert multiple rows in to the table with the same query, there will be rows that do not get updated.

But... with a little re-write, you can get the trigger to handle multiple rows. Something like this:

ALTER trigger [dbo].[Internal_Update]
on [dbo].[PL_TRANSACTIONS]
after insert
as
        
  update pl_transactions
  Set    pl_transactions.pt_internal_ref = pl_transactions.pt_batch_ref
  From   pl_transactions
         Inner Join Inserted I
           On pl_transactions.[!]PrimaryKeyField[/!] = I.[!]PrimaryKeyField[/!]

Notice I used a PrimaryKeyField column. You will need to change this to be whatever primary key you have on this table.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rafeman]

It does, thanks very much.

Will try tomorrow

 

[rafeman]

In fact, just tried it and it works!

Thank you again

 

[gmmastros]

you're welcome

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Just to add to George's advice some good links

check

http://www.lessthandot.com

website, WiKi, Data Management / SQL Server Programming Best Practices.

I would give you the exact link once the site will be back online (it's down right now )

 

[markros]

This is the link I meant

http://wiki.lessthandot.com/index.php/SQL_Server_Programming_Best_Practices

(scroll down to Triggers section)