perform multiple update and multiple insert synchorizely

• Sharul Nizam

  Ten Centuries

  Points: 1302

  More actions

  December 17, 2006 at 9:26 pm

  #94925

  Let's say, i've these following table:-

  tblstudent (Before)

  ID | Name  | Record_Type

  ----------------------------------------

  1 | James  | intake

  2 | Nora Jones | intake

  tblstudent_history (before)

  ID | Record_Type

  -------------------------------

  after execute the SQL statement containing Update (at tblstudent) and Insert (at tblstudent_history) together, the result shown as following:-

  tblstudent (After)

  ID | Name  | Record Type

  ----------------------------------------

  1 | James  | enroll

  2 | Nora Jones | enroll

  tblstudent_history (after)

  ID | Record Type

  -------------------------------

  1 | intake

  2 | intake

  i dont know, how to continue the SQL below,

  UPDATE tblstudent SET Record_Type='intake' WHERE ID IN ("1,2") then insert......????

  if need a trigger? anybody can show me?

• Russel Loski

  SSCertifiable

  Points: 7059

  More actions

  December 18, 2006 at 3:35 am

  #678589

  create trigger myupdatetrigger on tblStudent for update

  as

  insert into tblstudent_history

  select id, record_type from deleted

  go

  -- I would suggest adding a date field to your history.

  -- The "deleted" element is a view of everything before

  -- the update

  Russel Loski, MCSE Business Intelligence, Data Platform