Trigger from one table to update another 2

[d222222]

We have a field in the rm (room) table that counts how many employees occupy the room according to the em (employee) table.

We want to create a trigger to update the rm.count_em field when a change is made to the em (employee) table.

It also uses the bl (building) field, fl (floor) field to distinquish which room it is referencing. The bl, fl and rm fields are in both the em and rm tables. The em_id field is also in both tables.

I keep getting an error message that says "Subquery returned more than 1 value". I know it is because the subquery updates all of the records for rm.count_em and apparently it is trying to put it all into the one record that is updated in the em table but I can't figure out how to get it to work.

This is the code I was using:

UPDATE rm
SET rm.count_em = (SELECT Count(em_id) AS EmployeeHeadcount
		  FROM em, rm
		  WHERE em.bl_id = rm.bl_id AND em.fl_id = rm.fl_id AND em.rm_id = rm.rm_id
		  GROUP BY rm.bl_id, rm.fl_id, rm.rm_id)
FROM em, rm
WHERE em.bl_id = rm.bl_id AND em.fl_id = rm.fl_id AND em.rm_id = rm.rm_id

Any help would be appreciated.

 

[gmmastros]

try this:

Update rm
set    rm.count_em = A.EmployeeCount
From   rm
       Inner Join (
         Select em.bl_id, em.fl_id, em.rm_id, Count(*) As EmployeeCount
         From   em
         Group By em.bl_id, em.fl_id, em.rm_id
         ) As A
         On  em.bl_id = rm.bl_id 
         AND em.fl_id = rm.fl_id 
         AND em.rm_id = rm.rm_id

If this works, and you don't understand, let me know and I will explain it to you.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

NEVER!!!
Program a trigger with assumption that ONLY ONE row will be affected!
Triggers are fired after the WHOLE job is completed, so you may have many rows affected in that trigger.
Also try to use JOINS instead of that syntax.
This example is NOT program for triggers (there you should use DELETED and INSERTED tables:

UPDATE rm
SET rm.count_em = Tbl1.Cnt
FROM Rm
INNER JOIN (SELECT Bl_Id, Fl_Id, Rm_Id, Count(em_id) AS Cnt
                   FROM em
            GROUP BY bl_id, fl_id, rm_id) Tbl1
ON em.bl_id = tbl1.bl_id AND 
   em.fl_id = tbl1.fl_id AND 
   em.rm_id = tbl1.rm_id

(NOT TESTED!!!!!!!!)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[d222222]

Thank you for your response. I tried it and got an error message that says that the rm.bl_id, rm.fl_id & rm.rm_id could not be bound.

It might also be because of the code at the top of the trigger that references the em table. This is what I tried:

ALTER TRIGGER [afm].[employee_update]
ON [afm].[em]
FOR INSERT, UPDATE, DELETE
AS

Update rm
set    rm.count_em = A.EmployeeCount
From   rm
       Inner Join (
         Select em.bl_id, em.fl_id, em.rm_id, Count(*) As EmployeeCount
         From   em
         Group By em.bl_id, em.fl_id, em.rm_id
         ) As A
         On  em.bl_id = rm.bl_id 
         AND em.fl_id = rm.fl_id 
         AND em.rm_id = rm.rm_id

 

[d222222]

To bborissov

I was NOT trying to code it according to just one row being affected. I was trying to explain that I think that's what my code was doing but NOT what I wanted. All I wanted it to do is update the count in the rm table after ANY change is made to the em table.

I tried your code and also got the error that the fields could not be bound but it said they were from the em table.

 

[gmmastros]

my mistake. I used a derived table with an alias (A) but didn't use the alias in the on clause.

ALTER TRIGGER [afm].[employee_update]
ON [afm].[em]
FOR INSERT, UPDATE, DELETE
AS

Update rm
set    rm.count_em = A.EmployeeCount
From   rm
       Inner Join (
         Select em.bl_id, em.fl_id, em.rm_id, Count(*) As EmployeeCount
         From   em
         Group By em.bl_id, em.fl_id, em.rm_id
         ) As A
         On  [!]A[/!].bl_id = rm.bl_id
         AND [!]A[/!].fl_id = rm.fl_id
         AND [!]A[/!].rm_id = rm.rm_id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

There was a minor typo in Boris code:

UPDATE rm
SET rm.count_em = Tbl1.Cnt
FROM Rm
INNER JOIN (SELECT Bl_Id, Fl_Id, Rm_Id, Count(em_id) AS Cnt
                   FROM em
            GROUP BY bl_id, fl_id, rm_id) Tbl1
ON Rm.bl_id = tbl1.bl_id AND
   Rm.fl_id = tbl1.fl_id AND
   Rm.rm_id = tbl1.rm_id

which you could have probably caught by yourself looking a bit closer at it.

 

[d222222]

Thank you so much gmmastros!! It worked!!!!!! Thank you sooooooo much. I found some code on the internet that said to use an Inner Join but I still couldn't get it right.

Thanks for your help!!!

 

[d222222]

Thanks markros. I tried that and it worked too. You're right, I could have caught that if I'd looked at it closer. And you didn't make me feel like I was being yelled at.