changing a field''s value when user updates data

Hello,
I am working on a project that involves one part where a field's value needs to be changed when the user updates the record. Here is the situation in detail:
There is an InputData table where the user enters new records or changes existing records. There is a field called "calculated" in this table which has a default value of 'no'. A stored procedure runs math calculations on all the InputData records where the calculated field = 'no'. At the end of this stored procedure, it sets the calculated field = 'yes'. When new records are added by the user their "calculated" field value is 'no' by default so that the next time the stored procedure is executed, it only runs the math calculations on the new records. The problem is, if a user changes an existing record, the "calculated" field needs to be changed from 'yes' to 'no' so that the stored procedure recalculates the math for the modified record. How do I change the value from 'yes' to 'no' on records that the user modifies?
Thanks.

For changing the field value from ‘Yes’ no ‘No’ you can use the trigger (for update).

|||

Mani is correct. Here is an example of the update trigger.

e.g.

Code Snippet

create trigger _tr on InputData

for update

as

if @.@.rowcount=0 return;

update tb

set calculated = 'no'

from InputData tb join Inserted i on tb.[pk] = i.[pk]

where tb.calculated='yes'

go

|||

Hi!

You can directly include "Calculated ='no'" statement within the your update command itself only, based on the condition only. otherwise all the records will get updated.

eg:

Update InsertData
Set
Column1 = value1,
Column2 = value2,
...,
Calculated ='no'
WHERE <Condition>

I hope it'll solve your problem and please let me know if I'm wrong.

Thanks & Regards,
Kiran.Y

|||I have some questions about using a trigger before I attempt to implement one...

1. Will the code snippet, using the Inserted table, work in SQL version 8?

2. Which database holds the table called Inserted? Is it the one I'm working in or the tempdb or one of the other system databases?

3. Do I store the trigger as a stored procedure?

4. My project consists of SQL tables with an MS Access front end. When the user updates the record via MS Access will the stored procedure containing the trigger automatically run? (This question assumes the trigger code will be stored as a stored procedure...if this is wrong, please do correct this assumption).

5. What does if @.@.rowcount = 0 return; do?

Thanks in advance.|||

1. Yes. Sql2k does support trigger.

2. Inserted (or deleted) table is a special memory-resident table that can only be accessed in a trigger.

3. Trigger is just another object in sqlserver, similar to stored procedure.

4. Yes. Trigger is an event based procedure. It's tied to an event of the table (insert/update/delete).

5. This line tells the system to go ahead and exit and skip the rest of the code within the trigger because there is no row affected by the event.

http://msdn2.microsoft.com/en-us/library/aa258254(SQL.80).aspx

|||Your reply has helped to clarify things for me...thank-you.

I put the trigger in place on the InputData table and it works beautifully to set calculated = 'no' when the user makes an update to the data. However, when I attempt to assign 'yes' to the calculated field after the calculation stored procedures run, it will not. I am assuming this is because trying to set calculated = 'yes' constitutes an update to the table and per the trigger, any update to the row says to make calculated = 'no'. How do I get the trigger to fire when the update is made to any field in the table except for the calculated field?
|||

You can use IF UPDATE() clause inside the trigger to check.

e.g.

Code Snippet

IF UPDATE(calculated)

PRINT ('column [calculated] is modified')

|||As a follow up to this thread, here is how it worked out...

It seems I could not get this to work as I'd pictured using the calculated field (alas, I am a beginner so these things happen). I thought of a different approach to handle this problem. It works as follows:

I removed the calculated field from the InputData table, then created the following trigger:

Code Snippet

create trigger removeoldOutputData on InputData
for update
as
if @.@.rowcount=0 return;

delete from OutputData
where primarykey in (select primarykey from Inserted)

This trigger fires whenever a record is edited in the table InputData.

Then, the stored procedures that perform the math calculations specify which InputData records need to have calculations performed on them by comparing which primary keys in InputData do not yet have any records in OutputData. Here is a generic sample of the code:

Code Snippet

select
field1, field2, field3, ...., fieldn

into #inputdataforcalculations

from InputData
where primarykey not in (select primarykey from OutputData)

--more code follows to perform calculations on those records that were put into --#inputdataforcalculations

This where clause prevents the calculation stored procedures from recalculating existing data that has not been changed.

On the Access front end the calculation stored procedures are called in a pass-through query. There is a macro that performs an OpenQuery on the pass-through query, then this macro is automatically run when the user closes the form (event: On Close) that's used for entering/editing/deleting records in the underlying InputData SQL table.

I've run some tests on this approach and it appears to be functioning OK.

Thanks to all contributors above for my first lesson on triggers.

sql