nested update triggers which update multiple rows
-
This one seems like it should work. Imagine a heirarchy of 3 tables - department, team, person. Each with secondary keys up (person has team_id, team has department_id). A department can have multiple teams, a team can have multiple persons. Each table has an 'active' flag (bit) column.
department and team have triggers -
Department: if update(active), update team set activ = @myActive where team.department_id = @myId (nocount on)
team: if update(active), update person set activ = @myActive where person.team_id = @myId (nocount on)
nested tables are enabled and the recurrsion works fine (weather initiated from team (1level) or department (2levels)).
However when initiated from department and the department trigger has to update multiple rows in team (which should trigger update multiple rows in person) I get the [Subquery returned more than 1 value] error...
(team to multiple rows in person works)
ideas ? I'm pretty new to sql server and triggers for that matter ... Do I need to run a sp from the trigger ? or a curser (I don't know anything about them)? seems like overkill for a simple update ...
thanx
-
>>[Subquery returned more than 1 value] error
That usually has more to do with what you're doing in the code... you should post it so we can take a look for you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, sorry for the delay - was out of town.
ok, so we have departmentunit, team, and person in that heirarchy from top to bottom ... triggers on departmentunit and team:
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
CREATE TRIGGER DepartmentunitUpdateActiv ON dbo.departmentunit
FOR UPDATE
AS
set nocount on
/*
when departmentunit.activ is changed, update team
set team.activ = departmentunit.activ
where team.departmentunit_id = departmentunit changed
*/
declare @newStatus varchar
declare @theId int
if update(activ)
begin
select @newStatus = (select activ from Inserted)
select @theId = (select departmentunit_id from Inserted)
update dbo.team set activ = @newStatus where departmentunit_id = @theId
end
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
CREATE TRIGGER TeamUpdateActiv ON dbo.team
FOR UPDATE
AS
set nocount on
/*
when team.activ is changed, update person set person.activ = team.activ
where team_id = team changed
*/
declare @newStatus varchar
declare @theId int
if update(activ)
begin
select @newStatus = (select activ from Inserted)
select @theId = (select team_id from Inserted)
update dbo.person set activ = @newStatus where team_id = @theId
end
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
They seem pretty straight forward but I am new at this so ...
-
Man, I got you !!!
The problem is with the following lines:
select @newStatus = (select activ from Inserted)
select @theId = (select departmentunit_id from Inserted)
B'coz u are assigning a whole recordset to a varchar/int variable. You should change the above lines to:
select @newStatus = activ from Inserted
select @theId = departmentunit_id from Inserted
Check this out !!
-
Thanks Harsh,
That helped! but only a bit ... now it runs without errors.
assume: 1 row to affect in departmentunit, 3 rows in team, 20 rows in person
before:
update departmentunit -1 row affected, triggers update team -3 rows affected, error
after:
update departmentunit -1 row affected, triggers update team -3 rows affected, triggers update person -> only for last row (3 of 3) of previous update team ... not for the first two team_id updates but only the last one ...
...humph...
-
I haven't checked your logic but the problem with the triggers is that they are written in a RBAR (pronounced "ree-bar", my less than affectionate term for "Row By Agonizing Row") fashion. They need to be written to handle the whole set of records at once.
Here's how (again, without checking/changing your logic) to change one of the two... you can make similar changes to the other one...
CREATE TRIGGER TeamUpdateActiv ON dbo.team
FOR UPDATE
AS
set nocount on
/*
when team.activ is changed, update person set person.activ = team.activ
where team_id = team changed
*/
if update(activ)
begin
update p
set activ = i.active
from dbo.person p,
Inserted i
where p.team_id = i.team_id
end
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you Jeff !!
I see that I've got a bit to learn, I understand - but I don't understand. Guess I just need to keep moving forward
thanx,
/Nick
-
Not a problem, Nick. You had the right idea...
In SQL Server (very cool and very unlike Oracle), there are two tables that are temporarily formed (in TempDB) and available in every trigger... INSERTED and DELETED.
The INSERTED table contains new records for INSERTs and updated records for UPDATEs. The DELETED table contains deleted records for DELETES and "old" or "original" records for UPDATES.
Both tables are available regardless of trigger type or action but may be empty. For example, the DELETED table will be empty on INSERT triggers.
SQL Server also allows a FROM clause in the UPDATE statement.
You can do lot's of neat stuff in a trigger but I also want to remind you that triggers are (1) hidden code that most developers forget to look for and (2) will cause performance to decrease a bit when fired because, well, the server has extra stuff to do. Use triggers with some caution and restraint.
Your triggers are appropriate for what the problem definition is. Another reasonable use of triggers is for audit-logging. Just be careful to make sure the triggers are highly optimized and load tested not only for performance, but to also ensure they don't cause deadlocks or extended wait periods.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks again Jeff !
as you may have guessed this is for the admin gui of an application. the estimated usage which could activate the triggers is arounf 0.63% ... approx 1 hour out of 160 effective work hours per month ... pretty low ...
I guess what threw me was the p, i and from. the statement also seemed to follow a reverse logic and the p and i were never declared, so how did it know what they were ...?
thanx for the help!!
/Nick
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply