none
AFTER UPDATE trigger causes triplicate inserts

    Question

  • Background: The db consists of Tasks, Requests, and Workflow tables, with pertinent PK/FK assignments. Prior to implementing the trigger, I ran the below using T-SQL, and the result was a single occurrence of the new task ID. Now that the Trigger is stored, records are added to Tasks at least 3 times each. I've racked my brain, gone through other triggers, etc., and this is the only INSERT query relating to the below tables. Thoughts?

    INSERT INTO Tasks
    (taskorder, reqID, taskTitle, taskDescr, taskGenDate, taskAssignID, taskStatus)

    SELECT     fnNewTaskIso_1.NewTask, fnNewTaskIso_1.reqID, WorkFlowStep.wfstepTitle, 
    WorkFlowStep.wfstepDescript, GETDATE() AS taskGen, 1 AS AssignID, 0 AS TaskStatus

    FROM         dbo.fnNewTaskIso() AS fnNewTaskIso_1 INNER JOIN
    Requests ON fnNewTaskIso_1.reqID = Requests.reqID INNER JOIN
    WorkFlowStep ON fnNewTaskIso_1.NewTask = WorkFlowStep.wfstepOrder AND Requests.reqTypeID = WorkFlowStep.wfID

    Sunday, November 17, 2013 4:43 AM

Answers

  • Explanation:

    Each Request has a workflow (pre-defined) that it should follow. When the request is created, an Insert trigger populates the initial task of the workflow. I discovered an issue that seems to have resolved the above.... currently, when the initial task is closed (set to TRUE status), the next task in the workflow populates in open status (set to false).

    fnNewTaskIso_1 is a function (allows for quick read-only comparisons)

    • Marked as answer by moboyd Sunday, November 17, 2013 6:47 AM
    Sunday, November 17, 2013 5:21 AM

All replies

  • Hi,

    is your from clause right?.You are using actual tables, May be you should use inserted/Deleted tables.



    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Sunday, November 17, 2013 4:53 AM
  • Now that the Trigger is stored, records are added to Tasks at least 3 times each.

    Hello,

    I don't understand the intention of your trigger code and your issue; it inserts all data (=not filtered) from the tables fnNewTaskIso_1+Requests into Tasks


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Sunday, November 17, 2013 4:57 AM
  • Explanation:

    Each Request has a workflow (pre-defined) that it should follow. When the request is created, an Insert trigger populates the initial task of the workflow. I discovered an issue that seems to have resolved the above.... currently, when the initial task is closed (set to TRUE status), the next task in the workflow populates in open status (set to false).

    fnNewTaskIso_1 is a function (allows for quick read-only comparisons)

    • Marked as answer by moboyd Sunday, November 17, 2013 6:47 AM
    Sunday, November 17, 2013 5:21 AM
  • The issue was surrounding the 2 functions referenced for determining the next task to generate.

    fnNewTaskIso originally retrieved the maximum WorkFlowStep.taskorder with a completed status. Now, primary comparison is between a function to retrieve the Last Completed step, by Request ID, and a query for the Current Open Tasks, by Request ID. So, retrieving the delta of the 2, with all records from fnCompleted, and only NULLS from fnOpen, I was left with a list of Request IDs, and next Workflow step to generate, allowing me to execute the insert successfully, and with no issues.

    Sunday, November 17, 2013 6:52 AM