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.
| Author |
Topic |
|
iganesh2k2
Starting Member
6 Posts |
 Posted - 2007-06-19 : 03:38:00
|
| i am using sql server 2000. I have written update trigger on CORP_CAGE table to log the details in CORP_CAGE_LOG_HIST table,if any changes in EMP_SEQ_NO column.please find the structure of CORP_CAGE table:1.CORP_CAGE_SEQ_NO2.RECEIVED_DATE3.EMP_SEQ_NOCORP_CAGE table is having 50,000 records. the trigger "Check_Update" is fired when i am executing the following query from application which updates 10,000 records.UPDATE CORP_CAGE SET EMP_SEQ_NO=NULL WHERE EMP_SEQ_NO=111please find below the trigger,in that, trigger can easily find whether any UPDATE done in EMP_SEQ_NO column by using UPDATE FUNCTION.But,when it come to insert part, it takes more time(nearly 1 hour or sometimes it will hang.).For minimum records,this trigger is working fine.Create trigger Check_Update ON dbo.CORP_CAGE FOR UPDATE AS BEGINIF UPDATE(EMP_SEQ_NO) BEGIN INSERT CORP_CAGE_LOG_HIST ( CAGE_LOG_SEQ_NUM, BEFORE_VALUE, AFTER_VALUE, ENTRY_USER, FIELD_UPDATED ) SELECT i.CAGE_LOG_SEQ_NUM, d.RECEIVED_DATE, i.RECEIVED_DATE, i.UPDATE_USER, "EMP_SEQ_NO" FROM inserted i, deleted d WHERE i.CAGE_LOG_SEQ_NUM = d.CAGE_LOG_SEQ_NUM ENDENDplease help me on this for performance tuning the below query. |
|
|
Kristen
Test
22859 Posts |
|
|
|
|
|
|
|
Performance Tuning SQL query in Trigger