When is updated trigger executed?

Question

Post reply

When is updated trigger executed?

Thuan Pham-224808

Old Hand

Points: 329
More actions
July 12, 2006 at 2:23 pm

#366777

Hi
We have a Visual Foxpro program connecting to an SQL server database. I need to do a trigger so that when a person opens a SO, the due date will be automatically changed to tomorrow. This is easy but the problem is:
When a SO is open, the Visual Foxpro update information in other tables too using the due date. If I create a trigger to change the due date when SO is open, does the trigger change the due date of the SO before the Visual Foxpro program update other tables or after the Visual Foxpro program updates other tables ?
I would have a problem if the trigger is executed after the Visual Foxpro update other table. If this is true, is there anyway to make sure that the trigger is fired before instructions from Visual Program ?
Thank you
Thuan Pham

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

Look up triggers in books online.

Triggers only fire on Insert,Update,Delete.

they have to fire methods,

Instead of (insert,Update,Delete)

and

After (insert,Update,Delete)

I'm sure you can accomplish what you want in the after update trigger,

You describe you want the date to change when the so? is open. what does that mean, change date on open?

Why don't you have Foxpro change the date clientside, and save it on update?

Thuan Pham-224808 Old Hand Points: 329 More actions · Answer 2

Ray,

Thank you for the reply. I don't have access to changing the code on the client side. Which regard to opening the SO. When we first create a SO, it is in started status. Then we use client program to open the SO, change it status to Open, I need the SQL trigger to change the due date on the SO when we open it before the Visual Foxpro update other table. Otherwise, the due date on the SO and other tables will not match.

Thuan

Ian Yates SSCoach Points: 19738 More actions · Answer 3

You cannot think in terms of Visual Foxpro using local foxpro database files with local cursors. When apps are working with SQL Server they select the data then either perform an insert, update or delete (well at least to simulate your foxpro environment that's what happens).

So when FoxPro "opens" the record, nothing really happens. It is just when Foxpro saves the record that the update occurs. If FoxPro updates your table and then updates other tables then you can create your update trigger and have it work. The steps followed would then be

FoxPro updates main table

Main table trigger fires

FoxPro updates other tables

Thuan Pham-224808 Old Hand Points: 329 More actions · Answer 4

Ian

Thank you very much for the insightful reply. I will try it.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 5

I haven't tested this approach, but it might work. Is the DueDate field meaningful when the status is still "Started"? If not, you can use a view to show the due date as tomorrow when FoxPro first reads the record instead of worrying about the order of updates and whether FoxPro is aware that a trigger has modified the field.

Substitute a view for the SO table, where the DueDate field is defined as

CASE WHEN Status = 'Started' THEN cast(convert(varchar, getdate() + 1, 111) + ' 08:00:00' AS datetime) ELSE DueDate END

This makes the DueDate column non-updateable (at least through the view), so you also need an INSTEAD OF UPDATE trigger on the view to update the SO table.