SQL Server Performance Forum – Threads Archive
TRANSACTION in Trigger
I just inherit a database. A couple of tables have update/insert triggers on them. Like this:—————————————–
CREATE TRIGGER coUpd ON CO
FOR UPDATE
AS
IF ( UPDATE(current_task) )
BEGIN
BEGIN TRANSACTION
insert into Coitem (current_task)
values(‘current_task’)
COMMIT TRANSACTION
END
ELSE
IF ( UPDATE(allocated_start_date) )
BEGIN
BEGIN TRANSACTION
insert into Coitem (start_date)
values(start_date)
COMMIT TRANSACTION END
—————————————– it seems logical, yet I feel not right. I don’t think BEGIN /COMMIT TRANSACTION should be within TRIGGER since TRIGGER itself is a TRANSACTION. Is this right ? thanks, Lan
Hi Lan, I’ve not seen this used this way before and would probably remove it, after checking the code that does the update which causes the trigger to fire in the first place. the inserts into Coitem look a little odd to me too… the first inserts a hardcoded value, the second looks to be invalid? unless that bit is just an example? Cheers
Twan
OK, below is the complete code, hope you will not get bored. It is definitely not right, having cursor inside trigger. Yet there is something else not right, I cannot tell. I feel too many tranactions within triggers are unnecessary, yet need some guru for comments: ALTER TRIGGER Workflow_update1 ON dbo.Workflow
FOR UPDATE
AS — If the Current Task is being updated
IF ( UPDATE(current_task) )
BEGIN
BEGIN TRANSACTION INSERT Workflow_History (workflow_case_id, workflow_channel, object_id, object_type, current_task, previous_task, current_task_status, previous_task_status, creation_date, allocated_start_date, current_task_start_date, last_status_changed, user_id, work_category_id)
SELECT inserted.workflow_case_id, workflow_channel, object_id, object_type, current_task, previous_task, current_task_status, previous_task_status, creation_date, allocated_start_date, current_task_start_date, last_status_changed, user_id,
CASE WHEN object_type = ‘DOCUMENT GROUP’ THEN dg.work_category_id ELSE null END
FROM inserted
LEFT JOIN document_group dg ON dg.document_group_id = inserted.object_id COMMIT TRANSACTION END ELSE — If the allocated start date is being updated
IF ( UPDATE(allocated_start_date) )
BEGIN
BEGIN TRANSACTION DECLARE @max_workflow_history_id int
DECLARE @allocated_start_date datetime
DECLARE @user_id varchar(50)
DECLARE @new_current_task varchar(30)
DECLARE @old_current_task varchar(30)
DECLARE c_updated CURSOR FOR
SELECT a.max_workflow_history_id, a.allocated_start_date, a.user_id, a.current_task AS new_current_task, b.current_task AS old_current_task FROM (
SELECT MAX( Workflow_History.workflow_history_id ) AS max_workflow_history_id, inserted.allocated_start_date, inserted.user_id, inserted.current_task
FROM inserted LEFT JOIN Workflow_History ON inserted.workflow_case_id = Workflow_History.workflow_case_id
WHERE inserted.workflow_case_id = Workflow_History.workflow_case_id
GROUP BY inserted.workflow_case_id, inserted.allocated_start_date, inserted.user_id, inserted.current_task ) a
LEFT JOIN Workflow_history b ON a.max_workflow_history_id = b.workflow_history_id OPEN c_updated
FETCH c_updated INTO @max_workflow_history_id, @allocated_start_date, @user_id, @new_current_task, @old_current_task WHILE( @@FETCH_STATUS = 0 )
BEGIN IF @new_current_task = @old_current_task
BEGIN
— Old and new current tasks are same: simply update latest history row for Batch or Document Group
UPDATE Workflow_History
SET allocated_start_date = @allocated_start_date,
user_id = @user_id
WHERE workflow_history_id = @max_workflow_history_id
END
ELSE
BEGIN
— Old and new current tasks differ: insert new history row
INSERT INTO Workflow_history (workflow_case_id, workflow_channel, object_id, object_type, current_task, previous_task, current_task_status, previous_task_status, creation_date, allocated_start_date, current_task_start_date, last_status_changed, user_id, work_category_id )
SELECT a.workflow_case_id, a.workflow_channel, a.object_id, a.object_type, a.current_task, b.previous_task, b.current_task_status, b.previous_task_status, b.creation_date, @allocated_start_date, b.current_task_start_date, b.last_status_changed, a.user_id, b.work_category_id FROM
( SELECT workflow_case_id, workflow_channel, object_id, object_type, current_task, previous_task, current_task_status, previous_task_status, creation_date, allocated_start_date, current_task_start_date, last_status_changed, user_id
FROM inserted
) a
LEFT JOIN
( SELECT workflow_history_id, wh.workflow_case_id, wh.object_id, wh.previous_task, wh.current_task_status, wh.previous_task_status, wh.creation_date, wh.allocated_start_date, wh.current_task_start_date, wh.last_status_changed, wh.work_category_id
FROM Workflow_history wh
WHERE workflow_history_id = @max_workflow_history_id
) b
ON a.workflow_case_id = b.workflow_case_id
WHERE b.workflow_history_id = @max_workflow_history_id
END FETCH c_updated INTO @max_workflow_history_id, @allocated_start_date, @user_id, @new_current_task, @old_current_task
END CLOSE c_updated
DEALLOCATE c_updated COMMIT TRANSACTION END
Well, you can use ROLLBACK TRANSACTION and RETURN in a trigger for stuff that cannot be handled in constraints. There is an implied transaction, so you don’t need to start an explicit transsaction – just issue the rollback. Not sure why you would use a TRANSACTION without a ROLLBACK …
]]>