Update row values on insert

• tscott

  Ten Centuries

  Points: 1215

  More actions

  November 10, 2005 at 11:39 am

  #169732

  I'm trying to write a trigger (or any other methodology) whereas when a user inserts a new record, one value in the row automatically equals another input value when certain conditions are met. Per example, I'm inserting a new row into the table and if value A is greater than 100, set value B = 10% of value A (in the same record). Thusly, if a person's starting salary is 100000, set the bonus field to 10000. Any ideas would be appreciated!

  Thanks!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  November 10, 2005 at 10:04 pm

  #603363

  two ways to tackle this: a computed column is my suggestion, but you can use a trigger if you want to:

  create table xample(xid int identity(1,1) not null primary key,

  rateofpay money,

  bonuspay money)

  create table xample2(xid int identity(1,1) not null primary key,

  rateofpay money,

  bonuspay money,

  calculatedvalue as case when rateofpay > 10000 then rateofpay *.10 when rateofpay <= 10000 then rateofpay *.05 end )

  create trigger TR_xample on xample

  for insert as

  begin

  update xample set bonuspay = 0.10 * isnull(rateofpay,0) where isnull(rateofpay,0)<10000 and xid in(select xid from inserted)

  update xample set bonuspay = 0.05 * isnull(rateofpay,0) where isnull(rateofpay,0)>=10000 and xid in(select xid from inserted)

  end

  insert into xample2(rateofpay) values(100000)

  insert into xample(rateofpay) values(6500)

  insert into xample(rateofpay) values(90000)

  insert into xample(rateofpay) values(null)

  select * from xample

  select * from xample2

   

   

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!