Easy Trigger Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Easy Trigger Help

Author

Topic

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-30 : 11:33:19

I have two columns in a table [Suggested_Sell_Price] and [Cost]. Every time a record in the column of [Suggested_Sell_Price] is updated I want a trigger to update the [Cost] column of the same record with the following logic. [Cost] = [Suggest_Sell_Price] * .75

Can I do this within a trigger or do I have to create a stored procedure that I then reference in the trigger? Any help would be much appreciated.

Thanks,

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-30 : 12:46:46

No need to call SP.
A Trigger could do that.
But you can create your table with column that is computing its value automatic.
Don't know the english word for that...maybe computed column.
See this example:


create table test
(
 [Suggested_Sell_Price] money,
 [Cost] as isnull(Suggested_Sell_Price,0) * 0.75
)
 
insert test([Suggested_Sell_Price])
select 10.00 union
select 99.59
 
select * from test

drop table test

--Output:
Suggested_Sell_Price  Cost
--------------------- ---------------------------------------
10,00                 7.500000
99,59                 74.692500

(2 row(s) affected)

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-04-30 : 12:51:10

Not sure what the German is for it, but in English it is indeed a computed column.

Jim

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-04-30 : 12:56:33

Hi Jim,
it is a hard days evening

While typing that I don't know the english notion suddenly I was sure it is computed column.
But I was too lazy to retype

THX
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

benking9987
Posting Yak Master

124 Posts

Posted - 2009-04-30 : 13:53:34

Thank you for your help on this. Is there actually a way to do this based on a trigger instead of a computed column? My reason is that MOST of the time the above formula holds. However, there are cases where I need to input a number different than what the column would compute with what you have given me above. In those instances, it would be nice for the trigger to provide a "Starting Point" on pricing, but would constrain the values of the field to the result of the formula ONLY.

Any ideas? Thanks again for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources