Calculating Trigger Syntax

[newsqler]

I'm a newbee to the community and to writing sql. I would like to write a new trigger for some fields in a table I created and was hoping someone could help me. I know how to start a new tigger through enterprise manager, but it's the syntax that I'm not familiar with at all. Basically, I have three number fields for each particular ID. I want to add all these three fields up into a "total field" I would like to have a trigger to do the calculation when at least one of the three number fields is populated. Any ideas? Your help is very greatly appreciated. Thanks so much.

newsqler

[ronverdonk]

For the MySQL trigger syntax, and creating and dropping triggers, see Chapter 19. Triggers . Be aware that this works on MySQL 5.1 and up only!

Ronald :cool:

[newsqler]

Thanks, I'll check this out. I'm sorry, I should have mentioned that I'm using SQL 2000. Will that make a difference? Thanks for your help.

[ronverdonk]

Then you have asked your question in the wrong forum. Therefore I have shown you the MySQL information.
For SQL Server 2000 you must look in the appropriate forum. I will transfer this question to that forum now.

Ronald :cool:

[almaz]

For your task calculated field is the best choice:

create table #a
(
 id int identity(1, 1),
 val1 int not null default (0),
 val2 int not null default (0),
 val3 int not null default (0),
 total as (val1 + val2 + val3) 
)
 
insert #a default values
insert #a default values
insert #a default values
insert #a default values
 
select * from #a
update #a set val2=id
select * from #a
 

[newsqler]

This is great, thank you so much for your help. So, using your code, I already have a table called 'TABLE1' and three fields in that same table 'FIELD1', 'FIELD2', 'FIELD3' that I want to sum into 'FIELD4'. How would I use your code with this scenario? Thanks again so much.

[almaz]

You should create FIELD4 as a calculated field. For your scenario it will be:

alter table TABLE1 drop column FIELD4
alter table TABLE1 add FIELD4 as (FIELD1 + FIELD2 + FIELD3)

[newsqler]

I tried this out and got an error message. Here is the code and the error message I received:

code:

CREATE TRIGGER [name_of_trigger] ON [dbo].[table1]
FOR UPDATE
AS

alter table table1 drop column field4
alter table table1 add field4 as (field1 + field2 + field3)

error message:

odbc--update on a linked table 'dbo_table1' failed.
[microsoft][odbc sql server driver][SQL server] alter table drop column failed because column 'field4' does not exist in table 'table1' (#4924)

Any ideas? Thanks so much.

[almaz]

it should not be a trigger, just execute it once and that's all

[newsqler]

However, I need it as a trigger because records will be changing all the time. the trigger would help us calculate the total of the three fields every time any of the fields change.

[b1randon]

However, I need it as a trigger because records will be changing all the time. the trigger would help us calculate the total of the three fields every time any of the fields change.

A calculated field's value is dynamic and saves the overhead of rewriting the record every time. The calculated field will update itself as the values are added.