help to get the output with the help of trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

help to get the output with the help of trigger

Author

Topic

tsaliki
Starting Member

19 Posts

Posted - 2013-09-20 : 08:06:15

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)

select AssignedToRstEmpID,COUNT(AssignedToRstEmpID)as Noofcount from RStblJobs
group by AssignedToRstEmpID

i want the noofcount from the above statement to be inserted into usersrsdesk.noofhighprtjobs column.Whenever i update rstbljobs table then also the same count should be reflected here.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-09-22 : 04:09:39

[code]
CREATE TRIGGER Trg_rstbljobs_IUD
ON rstbljobs
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE u
SET u.noofhighprtjobs = u.noofhighprtjobs + tmp.Cnt
FROM UsersRSDesk u
INNER JOIN (SELECT i.AssignedToRstEmpID,COUNT(*) AS Cnt
FROM INSERTED i
LEFT JOIN DELETED d
ON d.AutoID = i.AutoID
WHERE i.AssignedToRstEmpID <> d.AssignedToRstEmpID
OR d.AssignedToRstEmpID IS NULL
GROUP BY i.AssignedToRstEmpID
)tmp
ON tmp.AssignedToRstEmpID = u.userid
END
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources