StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCREATE TRIGGER is taking more than 30 minutes on SQL Server 2005
text
Body
copied!<p>On our live/production database I'm trying to add a trigger to a table, but have been unsuccessful. I have tried a few times, but it has taken more than 30 minutes for the create trigger statement to complete and I've cancelled it. </p> <p>The table is one that gets read/written to often by a couple different processes. I have disabled the scheduled jobs that update the table and attempted at times when there is less activity on the table, but I'm not able to stop everything that accesses the table.</p> <p>I do not believe there is a problem with the create trigger statement itself. The create trigger statement was successful and quick in a test environment, and the trigger works correctly when rows are inserted/updated to the table. Although when I created the trigger on the test database there was no load on the table and it had considerably less rows, which is different than on the live/production database (100 vs. 13,000,000+).</p> <p>Here is the create trigger statement that I'm trying to run</p> <pre><code>CREATE TRIGGER [OnItem_Updated] ON [Item] AFTER UPDATE AS BEGIN SET NOCOUNT ON; IF update(State) BEGIN /* do some stuff including for each row updated call a stored procedure that increments a value in table based on the UserId of the updated row */ END END </code></pre> <p>Can there be issues with creating a trigger on a table while rows are being updated or if it has many rows? </p> <p>In SQLServer triggers are created enabled by default. Is it possible to create the trigger disabled by default? </p> <p>Any other ideas?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload