Trigger Help

[sam4help]

Dears,

I have one two tables

jobMaster
- [jobID]
- [jobStatus]

jobMasterDetails
- [jobID]
- [jobTaskID]
- [jobTaskStatus]

jobMasterDetails contains multiple task against jobID present in jobMaster.
Once every Task Completes the [jobTaskStatus] is changed to '10' or '11' for Cancelled.

Now I need to monitor this once all the task status changes to Completed or Cancelled I need to makes the [jobStatus] in jobMaster as JOB COMPLETED.

Can anyone help and guide in this,

Thanking you in advance,

Best Regards,

Sam

 

[sam4help]

CAME UP WITH THIS TRIGGER NOW TESTING ..

CREATE TRIGGER jobMasterDetails_trg_Upd ON jobMasterDetails
AFTER UPDATE 
AS
	Declare @updatedjobID int = (SELECT jobID FROM inserted);
	
	Declare @totalTaskCount int
        Declare @completedTaskCount int
    
BEGIN
   SET @totalTaskCount = 
        (SELECT COUNT(*) 
         FROM jobMasterDetails 
         WHERE jobID = @updatedjobID);
           
   SET @completedTaskCount = 
        (SELECT COUNT(*) 
         FROM jobMasterDetails 
         WHERE (jobID = @updatedjobID) AND jobTaskStatus IN (9,10));
END;
UPDATE jobMaster SET jobStatus=4 WHERE jobID=@updatedjobID;
GO

 

[sam4help]

Final got it working May Help Some1

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[jobMasterDetails_trg_Upd] ON [dbo].[jobMasterDetails]
AFTER UPDATE 
AS
	Declare @updatedjobID int = (SELECT jobID FROM inserted);
	
	Declare @totalTaskCount int
    Declare @completedTaskCount int
    
BEGIN
   SET @totalTaskCount = 
        (SELECT COUNT(*) 
         FROM jobMasterDetails 
         WHERE jobID = @updatedjobID);
           
   SET @completedTaskCount = 
        (SELECT COUNT(*) 
         FROM jobMasterDetails 
         WHERE (jobID = @updatedjobID) AND jobTaskStatus IN (9,10));
END;

IF (@totalTaskCount=@completedTaskCount)
BEGIN
	UPDATE jobMaster SET jobStatus=4,jobLastStatus=jobStatus WHERE jobID=@updatedjobID;
END;

GO

 

[gmmastros]

I would STRONGLY encourage you to take another look at this trigger. The problem with this trigger is that it only accommodates 1 row getting updated at a time. Even if your current process is such that only 1 row IS updated at a time, you cannot guarantee that this will forever be true.

When you write a trigger, you MUST take in to account that fact that there may be multiple rows updated in the same session.

If I understand correctly, you want to set a couple values in jobMaster when the count of all detail rows matches the count of all details rows where the status is 9 or 10. Is this correct?



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Based on my understanding, I think the trigger you want is this:

CREATE TRIGGER [dbo].[jobMasterDetails_trg_Upd] ON [dbo].[jobMasterDetails]
AFTER UPDATE 
As
    SET NOCOUNT ON

    ; With CompletedTasks As
    (
        Select  JobId
        From    jobMasterDetails
                Inner Join Inserted I
                    On jobMasterDetails.JobId = I.JobId
        Group By JobId
        Having Count(*) = Count(Case When jobTaskStatus In (9,10) Then 1 End)
    )
    Update  jobMaster
    Set	    jobMaster.Status = 4,
            jobMaster.jobLastStatus = jobStatus
    From    jobMaster
            Inner Join CompletedTasks
              On jobMaster.JobId = CompletedTasks.JobId

There are a couple neat little tricks here.

In the CTE...

[red]Having Count(*) = Count(Case When jobTaskStatus In (9,10) Then 1 End)[/red]

Note that Count(*) will count the number of rows in the table for the column(s) in the group by statement. In this case Count(*) will be the total number of rows for each JobId.

Also notice this: Case When jobTaskStatus In (9,10) Then 1 End
If the jobTaskStatus is 9 or 10, this case/when statement will return the value 1, otherwise it will return NULL.

[red]Count(Case When jobTaskStatus In (9,10) Then 1 End)[/red]
The count aggregate will not count NULLS, so this will basically return the number of rows (per JobId) where status is 9 or 10.

The CTE part joins to the inserted table so we will not be looking at the entire table. The having clause filters the results to only those where the count of all rows (per jobid) matches the count of rows (per jobid) where status is in 9 or 10.

The CTE will return 1 column (JobId). The only JobId's returned will be those that are updated (from the inserted table) and where all of the status's are 9 or 10.

The outer query does an inner join to the CTE results so that jobMaster table will only update rows that have a matching JobId from the CTE.

Does this make sense?




-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Thanks George for your kind review,
Currently there is only one button that allows to change this status for jobMaster table; also jobID is unique id so only one record will be updated in master. And I am only changing two field values where one value is just keeping the last value track from current updating field.

In other words I am checking if all assigned tasks (total no of task for that job id) is closed or something may be cancelled (which cannot be closed). So once these all done then it will close the main job (that's the task of this trigger)

Hope I am clear enough,

Best Regards,

Sam

 

[sam4help]

Thanks George for all the efforts, will need some time to think over it and understand
Surely get back to you .. thanks again

Regards,

Sam

 

[gmmastros]

Based on your description, the trigger I posted should do the trick. There may be some minor tweaking, but it should work.

It's also likely to be faster, and can handle multiple rows. I believe that it's a mistake to put any code in to production that you are not comfortable with or understand. To that end... if there is anything that you don't understand, please let me know and I will explain it.

As always, I encourage you to test this on a copy of your database prior to implementing this in production.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sam4help]

Sure thanks a lot George,
That's why I am taking sometime to understand what and how its doing before I put on live.

Best Regards,

Sam