INSERT Trigger Delay - 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)

INSERT Trigger Delay

Author

Topic

PBoy
Starting Member

22 Posts

Posted - 2011-01-26 : 19:22:02

Hi All,

Im wondering is there a way to tell a insert trigger to await a set time before its is actioned.

For example :
I have a simple trigger on a header table (Below) that works however becuase Im looking for a sum from a details table it brings back a null value this happens because the trigger has been action before the data have been inserted into the details table. So is there a simple way to tell the trigger to wait 10 secs or so and then kick in?

BEGIN
INSERT INTO urcapproclog (cur_amount,voucher_no, voucher_type, apar_id, user_id, period, ext_inv_ref, description, arrival_date, order_id)
SELECT (SELECT SUM(amount) as amount from attdetail where voucher_no = SUB.voucher_no) AS Role,*
FROM (SELECT voucher_no,voucher_type,apar_id,user_id,period,ext_inv_ref,Travel_Type,last_update,confirm_flag
FROM attheader) AS SUB
where LEN(sub.voucher_no) = '9' and sub.User_id = '030023809' and last_update >= '2011-01-27 00:00:00.000'
END

Im sure I can do this from the detail table and link back to the header table but there are some insert triggers (more complex) on that table and Im not wanting to add this to it.

Any help would be great

Cheers
PBoy

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-27 : 09:19:25

Have a look at the waitfor delay statement.

Bear in mind that while the trigger is running it will be holding locks and the firing statement will not complete.
If the same connection is updating the header and then the detail it is no use putting in the delay - that will just delay the detail update as well.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2011-01-27 : 09:25:39

putting waitfor in a trigger seems like a good way to kill the perf of your system.

otoh, later on when you are asked to fix the perf, you can remove the delays and be the hero! :)

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources