March 2004 Blog Posts
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(''' +...
Deleted due to intensely violent and graphical nature of post.
(In other words, I don't want my boss to read it.)
:)
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...