September 20, 2013 at 6:18 am
i have a table with records
create table rstbljobs
(
AutoID identity(1,1),
AssignedToRstEmpID int,
priorityid int,
Jobstatusid int
)
Insert into rstbljobs values ( 147,202,374)
Insert into rstbljobs values ( 169,214,374)
Insert into rstbljobs values ( 170,202,374)
Insert into rstbljobs values ( 176,202,374)
Insert into rstbljobs values ( 182,202,374)
Insert into rstbljobs values ( 147,214,374)
Insert into rstbljobs values ( 183,202,374)
Insert into rstbljobs values ( 170,214,374)
Insert into rstbljobs values ( 182,214,374)
Insert into rstbljobs values ( 170,202,374)
Insert into rstbljobs values ( 147,214,374)
Insert into rstbljobs values ( 147,202,374)
Insert into rstbljobs values ( 169,202,374)
Insert into rstbljobs values ( 169,214,374)
i have another table UsersRSDesk
Create table UsersRSDesk
(
userid int,
IsRecruit bit,
status bit,
noofhighprtjobs varchar(50)
)
The records are
insert into UsersRSDesk values (147,1,0,null)
insert into UsersRSDesk values (169,1,0,null)
insert into UsersRSDesk values (170,1,0,null)
insert into UsersRSDesk values (176,1,0,null)
insert into UsersRSDesk values (182,1,0,null)
insert into UsersRSDesk values (183,1,0,null)
The query is as follows:
select AssignedToRstEmpID,COUNT(AssignedToRstEmpID)as Noofcount from RStblJobs
group by AssignedToRstEmpID
from the above query i want the noofcount from rstbljobs to be inserted into usersrsdesk.noofhighprtjobs column.Whenever i update rstbljobs table then also the same count should be reflected here.
September 20, 2013 at 6:41 am
I'm assuming that "UsersRSDesk" table will have all the "AssignedToRstEmpID" related data in that table.
following trigger will do the trick for u ...
CREATE TRIGGER dbo.TR_UsersRSDesk
ON dbo.rstbljobs
AFTER INSERT,DELETE,UPDATE
AS
BEGIN
SET NOCOUNT ON;
Update urd
set urd.noofhighprtjobs = isnull(a.Noofcount,0)
from UsersRSDesk urd
left join (select AssignedToRstEmpID,COUNT(AssignedToRstEmpID)as Noofcount
from RStblJobs
group by AssignedToRstEmpID
) a on urd.userid = a.AssignedToRstEmpID
END
GO
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply