March 2004 Blog Posts

Nested Triggers and Transactions

I ran across an interesting post on www.sqlteam.com that got me thinking about how transactions interact inside of nested triggers.  Since I'm trying to study how transactions work in SQL Server anyway, I thought I would give this a try. EXEC sp_configure BEGIN TRANSACTION TRAN_WRAP  BEGIN TRANSACTION CREATE_TABLE  IF (SELECT OBJECT_ID('djl_test_trigger_transactions')) IS NOT NULL   BEGIN    DROP TABLE djl_test_trigger_transactions   END   CREATE TABLE djl_test_trigger_transactions(   int_value INT PRIMARY KEY,   varchar_value VARCHAR(55)) COMMIT TRANSACTION CREATE_TABLE  BEGIN TRANSACTION TRIGGER1  DECLARE @sql NVARCHAR(4000)   SELECT @sql = '   CREATE TRIGGER [trg_djl_test_trigger_transactions_ins_1] ON [dbo].[djl_test_trigger_transactions]    FOR INSERT, UPDATE    AS      PRINT ''EXECUTING FIRST TRIGGER''    IF EXISTS(SELECT varchar_value FROM inserted WHERE varchar_value = ''' + 'aaa' + ''')    BEGIN         IF EXISTS( SELECT * FROM inserted WHERE int_value = 2)      BEGIN       RAISERROR(''' +...

posted @ Tuesday, March 30, 2004 7:48 PM | Feedback (2)

To bitch or not to bitch, that is the question.

Deleted due to intensely violent and graphical nature of post. (In other words, I don't want my boss to read it.) :)

posted @ Monday, March 29, 2004 12:01 AM | Feedback (5)

Nested Cursors Suck!!! Oh wait, so do non-nested cursors.

Well, I have to start with something, so here it is.  I would say I hope you enjoy it, but I don't really care.  I'm just grumbling about work.  (grin) At work the last couple weeks, I worked on fixing an old nightly batch update process that ran about 6.5 hours each night.  When I analyzed it, I saw a small problem, it had (count them) nested cursors FIVE LEVELS deep.  After rewriting the query to be two stored procedures with two monster select statements it ran in about 44 seconds. Lessons learned:  Always think set based.  Loops are sometimes needed, but a lot of times you can do...

posted @ Monday, March 22, 2004 11:13 PM | Feedback (2)