sql 7.0 delayed trigger

Question

HI all,

I have a trigger that i need to run after the insertion or update is done. I know there is a code that on 2000 saying after but 7.0 doesnt have that. How can i do this on 7.0

Answer 1

You're in luck - AFTER triggers in SQL Server 2000 are regular triggers in SQL Server 7.0, so just create a trigger, and it will fire after the insert or update. SQL Server 2000 adds INSTEAD OF triggers, but that's not what you want. The standard FOR trigger syntax in 7.0 gives you the same as the AFTER trigger syntax in 2000 - they just added the AFTER syntax to distinguish regular after triggers from the new INSTEAD OF triggers.

Answer 2

ok your saying the for trigger runs after the insertion and the update is done?

i dont think it works like that because i have a query that i put in the trigger which looks at the table it self to all the records and does some amount calculations. And this amount is different when i run the same query right after the insertion my self. I hope that make sense.

Answer 3

It is the same as an AFTER trigger in SQL Server 2000 yes - but on both versions an after trigger is not truly after, it is more like right at the end. The trigger runs before the transaction is committed so that it can roll the transaction back if necessary. You need to use the inserted and deleted tables to see what the rows that are being inserted and deleted are (for an INSERT trigger the inserted table holds the new rows that are being inserted - they are not in the table yet, for an UPDATE trigger the deleted table holds the rows that will be updated as they are before updating, and the inserted table holds the rows that are being updated as they will be after updating).

Answer 4

is there a way to run the code after all is done. after the data is committed. Since im running on the table my query on the table not the row it self. Or im going to use a temp table in the trigger to get the update done on it and run the query at that. but this will add some load on the trigger time. so if i could just run this right after everything is done. Any ideas.

Answer 5

You should write a stored procedure. Run the main update in the stored procedure followed by the other update code that you currently have in the trigger. Then call the stored procedure to perform the update.

Answer 6

let me see if i got this right your saying to change the program coding which on every update it should run this sp that has the trigger coding. If so, not possible because the coding that has all the updates to the table is all around the place there is more then 300 sp's in the system that needs modification. Not possible. I have to do something in the trigger.

 

 

 

Change in status: The trigger is ok, after inserting the changing to see what was happenning in the table i noticed the table is getting updated correctly in time. It was a codding mistake done in the main table update that was causing the mis calculations. (loong story) but i did fix the problem im good now. Thanks for the help