Create a SQL trigger that calculates a field on insert?

Hi,
I would like to set up an SQL Trigger on an MS sql Database 2008. I'm sorry I'm not very familiar with T-SQL  and I'm in the process of learning. How would I go about setting up a Trigger that Dynamically calculates a Field on insert?

([WATER_LENGTH] * [WATER_WIDTH] /43560) * [WATER_DEPTH]

This is the formula I would like to use to calculate the field [VOLUME_DIMEN]

GravitaZ24Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, first, you could make that field eventually a computed field, so you don't need a trigger.

anyhow:
create trigger trg_volme_dimen
 on yourtable
for insert
as
  update t
      set volume_dimen = ([WATER_LENGTH] * [WATER_WIDTH] /43560) * [WATER_DEPTH] 
    from yourtable t
    join INSERTED i 
      on i.primary_key_field = t.primary_key_field

Open in new window


INSERTED is the virtual table referring to the rows you inserted into the "yourtable", for which the trigger is raised.
the primary_key_field is the name of your primary key field in your table..

so, you just rename the tablename, primary_key_field, and eventually the trigger name as to your needs, and that should be it :)
 
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HainKurtSr. System AnalystCommented:
no need for trigger as angelIII suggested use computed column
or create a view like this

screate view as vMyTable as
select t.*, ([WATER_LENGTH] * [WATER_WIDTH] /43560) * [WATER_DEPTH]  as volume_dimen
from myTable t

and use vMyTable when you need that column...
0
lcohanDatabase AnalystCommented:
The best would be to directly insert the calculated value from whatever UI (business layer) code you have into a SQL.table.column. Triggers are hidden SQL code objects and may cause performance issues or other headaches in the future.
Second option would be indeed a computed column as angelIII allready suggetsed.
0
GravitaZ24Author Commented:
Thank you, I decided to scrap the trigger idea and use the computed column.

lcohan, the problem with the UI option is I'm using arcGIS and there isn't really an option for that
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.