February 15, 2013 at 7:11 am
Hi,
Very weird case I am facing at the moment.
I have a trigger that works well until the moment it needs to commit the changes.
Some explanation:
I need to populate a table "tbl_advertisers_products_per_sub_cat" when something changes in another table (tbl_advertiser_section_data).
Therefore, depending on the case (insert, update, delete), I have created 3 distinct triggers.
The trigger that causes me trouble is the one, linked to the "update" statements.
In order to debug that trigger, I run "update tbl_advertiser_section_data set advertiser_id = advertiser_id;".
Debugging the trigger, I see that everything goes fine until it needs to commit (the select statement gives the results I need to be inserted).
Anyone could tell me what's going wrong?
Many thanks in advance,
Here is the trigger:
ALTER TRIGGER [dbo].[trg_advertiser_upd]
ON [dbo].[tbl_advertiser_section_data]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [dbo].tbl_advertisers_products_per_sub_cat (item_id, item_type, category_name_prefix)
SELECT i.advertiser_id, 1, i.category_name
FROM INSERTED i
LEFT OUTER JOIN [dbo].tbl_advertisers_products_per_sub_cat AC
ON AC.item_id = i.advertiser_id AND AC.item_type = 1 AND AC.category_name_prefix = i.category_name
WHERE AC.item_id IS NULL;
END
February 15, 2013 at 7:52 am
boeledi (2/15/2013)
Hi,Very weird case I am facing at the moment.
I have a trigger that works well until the moment it needs to commit the changes.
Some explanation:
I need to populate a table "tbl_advertisers_products_per_sub_cat" when something changes in another table (tbl_advertiser_section_data).
Therefore, depending on the case (insert, update, delete), I have created 3 distinct triggers.
The trigger that causes me trouble is the one, linked to the "update" statements.
In order to debug that trigger, I run "update tbl_advertiser_section_data set advertiser_id = advertiser_id;".
Debugging the trigger, I see that everything goes fine until the "COMMIT TRANSACTION;" statement.
In order to check that everything works OK until there, I have inserted "SELECT * FROM tbl_advertisers_products_per_sub_cat;" just before the "COMMIT TRANSACTION;" statement and the result is OK... EXCEPT that it does not commit.
Anyone could tell me what's going wrong?
Many thanks in advance,
Holy cow this trigger is scary. I ran it through a formatter so it is legible.
ALTER TRIGGER [dbo].[trg_advertiser_upd] ON [dbo].[tbl_advertiser_section_data]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @advertiser_id BIGINT
,@category VARCHAR(50);
--
-- Declare the CURSOR to consider all possible "parallel" updates
--
DECLARE cur_onTrigger CURSOR
FOR
SELECT i.advertiser_id
,i.category_name
FROM INSERTED i;
-- *********************
-- * Start TRANSACTION *
-- *********************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- SET XACT_ABORT ON will cause the transaction to be uncommittable
-- when an error occurs.
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
--
-- Open the cursor and loop through all its records
--
OPEN cur_onTrigger;
FETCH NEXT
FROM cur_onTrigger
INTO @advertiser_id
,@category;
--
-- Start the main loop
--
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS (
SELECT 1
FROM [dbo].tbl_advertisers_products_per_sub_cat
WHERE item_id = @advertiser_id
AND item_type = 1
AND category_name_prefix = @category
)
BEGIN
INSERT INTO [dbo].tbl_advertisers_products_per_sub_cat (
item_id
,item_type
,category_name_prefix
)
VALUES (
@advertiser_id
,1
,@category
);
END;
--
-- Get next record from cursor
--
FETCH NEXT
FROM cur_onTrigger
INTO @advertiser_id
,@category;
END;
--
-- Close the cursor
--
BEGIN TRY
CLOSE cur_onTrigger;
DEALLOCATE cur_onTrigger;
END TRY
BEGIN CATCH
-- Silent mode
END CATCH
-- **********************
-- * Commit all changes *
-- **********************
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
-- Test whether the transaction is uncommittable
IF (XACT_STATE()) = - 1
BEGIN
--
-- Retrieve error information
--
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
,@ErrorSeverity INT = ERROR_SEVERITY()
,@ErrorState INT = ERROR_STATE();
--
-- Rollback transaction
--
ROLLBACK TRANSACTION;
--
-- Close the cursor
--
BEGIN TRY
CLOSE cur_onTrigger;
DEALLOCATE cur_onTrigger;
END TRY
BEGIN CATCH
-- Silent mode
END CATCH
--
-- We need to inform the user
--
RAISERROR (
@ErrorMessage
,@ErrorSeverity
,@ErrorState
);
END;
END CATCH;
SET XACT_ABORT OFF;
END
There is absolutely no need for a cursor here. There is no need to begin a transaction inside your cursor, the update statement already has a transaction so a commit or a rollback inside your trigger will commit or rollback the transaction that is already active. As a general rule it is not a good habit to squelch or suppress errors using an empty catch.
I know this is just a guess on my part but I think you can change this entire cursor based trigger to the following single insert statement.
ALTER TRIGGER [dbo].[trg_advertiser_upd] ON [dbo].[tbl_advertiser_section_data]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
BEGIN TRY
INSERT INTO [dbo].tbl_advertisers_products_per_sub_cat (item_id, item_type, category_name_prefix)
SELECT i.advertiser_id, 1, i.category_name
FROM INSERTED i
left join tbl_advertisers_products_per_sub_cat a on a.item_id = i.advertiser_id and a.category_name_prefix = i.category_name
where a.item_id is null
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE()
,@ErrorSeverity INT = ERROR_SEVERITY()
,@ErrorState INT = ERROR_STATE();
ROLLBACK TRANSACTION;
--
-- We need to inform the user
--
RAISERROR (
@ErrorMessage
,@ErrorSeverity
,@ErrorState
);
END CATCH
END
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 15, 2013 at 7:53 am
Well it seems that while I writing my response you edited the original trigger and posted almost exactly the same thing I was suggesting. 😀
Does this much simpler version still exhibit the same problem?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 15, 2013 at 10:06 am
Sean Lange (2/15/2013)
Well it seems that while I writing my response you edited the original trigger and posted almost exactly the same thing I was suggesting. 😀
Heh... I have users like that. Can you hit the hole of a rolling donut? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply