SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 help to get the output with the help of trigger
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tsaliki
Starting Member

India
19 Posts

Posted - 09/20/2013 :  08:06:15  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 09/22/2013 :  04:09:39  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000