Trigger Problem

Trigger Problem

• tom stucki

  Apart from the issues mentioned in the other replies, your code doesn't protect against simultaneous inserts since you are not serializing the update of the control table. The trigger code with corrections will look like:

  create trigger update_transID
  on transactions
  after insert
  as
  declare @id_value nvarchar(25)

  -- get xlock on row below so you can update it after the transactions are updated
  set @id_value = (select id_value from controls with(xlock) where id='trans_no')
  update transactions set trans_id= @id_value where trans_id in (select trans_id from inserted)
  update control set id_value = (@id_value+1) where id = 'trans_no'

  go

  Now, I am not sure if this is the correct logic also. It is hard to say given the information in your post. If you are generating transaction ids then why can't you add an identity column to the transactions table. It will be much more efficient, improves concurrency and doesn't require trigger logic like this.

  
  

  
  
  

• sekhar_nitt

  Thanks for the replay, Even this query is not working can you help me in this.
  
  

• Pluey

  Hi,
  
  you should consider a general design mistake that you have. Triggers are fired per statement NOT per row, so using your trigger (although you don’t use valid syntax in your update statement and you use different names for the same table control/controls)

  You should also do this the other way, first update the control table, then use the value for the update to the transaction table.

  HTH, jens Suessmeyer.
  
  ---
  http://www.sqlserver2005.de
  ---

  
  

• DMX

  If your original INSERT statement ALWAYS assigns the same trans_id to everything that is inserted (that is, trans_id is NOT the IDENTITY column, cause identity would assign 2 different values to 2 rows inserted at once), you can try to rewrite your update statement as follows:

  UPDATE transactions SET transactions.trans_id = @id_value WHERE transactions.trans_id IN (SELECT trans_id FROM inserted)

  
  

• empire29

  Thanks for the replay, there is a small problem when i execute trigger following statement is not updating the value to the trans_id in the transaction.

  update transactions set trans_id= @id_value where trans_id in (select trans_id from inserted)

  My requirement is, i want to update the @id_value in transactions table which is inserted recently.

  Can you help me in this regards.

  Thanking you

  
  

  
  
  
• Trigger Problem