May 30, 2009 at 6:13 pm
Hi
I would like a trigger for update ,
if I change the Manager of a master Task (e.g. TaskID 1), the Manager field of sub tasks (IDs 3 and 4) update to the new Manager?
ID --- AssoID---Task--- Manager
____________________________
1 --- NULL --- T1 ---- M1
2 --- NULL --- T2 ---- M2
3 --- 1 --- --- sT1 --- M1
4 --- 1 --- --- sT2 --- M1
5 --- 2 -- ---- sT1 --- M2
any idea?
Thanks
JK
May 30, 2009 at 6:51 pm
What you need in a trigger is to write the query to perform the update. Join the "inserted" table with the base table to perform the update.
We would like to make an attempt to write the trigger first and we can help you there.
May 31, 2009 at 7:17 am
Hello again,
and thanks for response
here my Idea:
CREATE TABLE [dbo].[Team](
[ID] [int] IDENTITY(1,1) NOT NULL,
[AssoID] [int] NULL,
Task [nvarchar](50) NULL,
Manager [nvarchar](50) NULL
) ON [PRIMARY]
GO
CREATE TRIGGER trig_updateM
ON Team
FOR UPDATE
AS
IF UPDATE(Manager)
BEGIN
declare @TID INT
declare @AsID INT
select @TID= id , @AsID=AssoID from inserted
IF @AsID IS NULL
BEGIN
UPDATE test
SET Manager = i.Manager
FROM inserted i INNER JOIN test t ON i.ID =t.ID
WHERE (t.AssoID = @AsID)
END
END
but i get the error:
Msg 207, Level 16, State 1, Procedure trig_updateM, Line 15
Invalid column name 'Manager'.
Thanks
May 31, 2009 at 11:03 am
The rules for the schema appear to be:
A task is a "master" task when the "AssoId" is NULL
A Sub-Task of a master task is identified by the constant "s" preceeding the master task value.
Some trigger behavior information:
Triggers are executed even when the rows affected are zero.
The "UPDATE( column_name)" only indicates that the column is part of the "UPDATE SET" columns list and not that the value has changed. To determine if the value has changed, join the inserted and deleted tables and add a where for inserted.column deleted.column. If the column allows NULL, additional comparisons to check when changed from null to not null or vice versa are needed.
CREATE Team_tau_manager ON dbo.Team
FOR UPDATE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
IF NOT UPDATE(Manager) RETURN
IF 0 = (SELECT COUNT(*) FROM INSERTED) RETURN
UPDATE dbo.Team
SET Manager = inserted.Manager
FROM inserted
JOIN deleted
ON deleted.id = inserted.id
JOIN dbo.Team
ON Team.Task = 's' + inserted.Task
WHERE inserted.AssoID IS NULL
AND deleted.Manager inserted.Manager
go
SQL = Scarcely Qualifies as a Language
May 31, 2009 at 1:53 pm
first of all thank you Carl, i tried your code but couldn't update eny row!
sorry a mistake in my code
here again my way but with error message :
No row was updated.
The data in row 1 was not committed.
Error Source: .Net SqlClient Data Provider.
Error Message: Maximum stored procedure, function, trigger, or view
nesting level exceeded (limit 32).
CREATE TRIGGER trig_updateM ON Team
FOR UPDATE
AS
IF UPDATE(Manager)
BEGIN
declare @TID INT
declare @AsID INT
select @TID= id , @AsID=AssoID from inserted
IF @AsID IS NULL
BEGIN
UPDATE Team
SET Manager = i.Manager
FROM inserted i INNER JOIN Team t ON i.ID = t.AssoID
WHERE (t.AssoID = @AsID)
END
END
May 31, 2009 at 5:21 pm
"Error Message: Maximum stored procedure, function, trigger, or view
nesting level exceeded (limit 32)."
As each stored procedure or trigger is invoked, the "nest level" is increased by one and SQL Server has a limit of 32 nest levels,which you code is exceeding.
The trigger as you have written will not give the desired results and will cause errors.
Here is the complete SQL with test cases and everything works fine.
CREATE TABLE [dbo].[Team](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[AssoID] [INT] NULL,
Task [NVARCHAR](50) NULL,
Manager [NVARCHAR](50) NULL
) ON [PRIMARY]
GO
CREATE TRIGGER Team_tau_manager ON dbo.Team
FOR UPDATE
AS
SET NOCOUNT ON
SET XACT_ABORT ON
PRINT 'Trigger Team_tau_manager executing at nest level ' + CAST(@@NESTLEVEL AS VARCHAR(8) )
IF NOT UPDATE(Manager) RETURN
IF 0 = (SELECT COUNT(*) FROM INSERTED) RETURN
UPDATE dbo.Team
SET Manager = inserted.Manager
FROM inserted
JOIN deleted
ON deleted.id = inserted.id
JOIN dbo.Team
ON Team.Task = 's' + inserted.Task
WHERE inserted.AssoID IS NULL
AND deleted.Manager inserted.Manager
GO
TRUNCATE TABLE dbo.team
GO
INSERT INTO dbo.Team
([AssoID] ,Task , Manager )
SELECT NULL , 'T1' , 'M1'UNION ALL
SELECT NULL , 'T2' , 'M2'UNION ALL
SELECT NULL , 'T3' , 'M3'UNION ALL
SELECT NULL , 'T4' , 'M4'UNION ALL
SELECT 1 , 'sT1' , 'M1'UNION ALL
SELECT 2 , 'sT1' , 'M1'UNION ALL
SELECT 1 , 'sT2' , 'M2'UNION ALL
SELECT 2 , 'sT2' , 'M2'UNION ALL
SELECT 2 , 'sT3' , 'M3'UNION ALL
SELECT 1 , 'sT3' , 'M3'UNION ALL
SELECT 1 , 'sT4' , 'M4'UNION ALL
SELECT 2 , 'sT4' , 'M5'
go
SELECT * FROM team
UPDATEdbo.team
SETManager = 'Mnew'
WHEREteam.Manager in ('M1','M2')
SELECT * FROM team
SQL = Scarcely Qualifies as a Language
June 1, 2009 at 2:30 am
great work Carl.
works fine.
Thank you very much
JK
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply