Need to pass a value in a SQL trigger from on Update statement to another within the same trigger
Hi,
I have a trigger that updates my detail table. I would like to use the same trigger to also update the parent table based on a value in the detail table. I keep getting an error "The multi-part identifier "Citations.LogDetails.Pare
The trigger works when I use a literal numeric value but not when I pass a value. Is there a way to pass the value from the detail table in the first update statement to the update statement in the second table?
Thanks
/****** Object: Trigger [Citations].[tgr_LogDetail
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[tgr_LogDetail
ON [Citations].[LogDetails]
AFTER UPDATE AS
UPDATE Citations.LogDetails
SET LastUpdatedOn = GETDATE()
WHERE LogDetailID IN (SELECT DISTINCT LogDetailID FROM Inserted)
UPDATE Citations.LogHeader
SET LogHeader.TotPlatesPerLog = (SELECT COUNT(DISTINCT(VehiclePlat
WHERE LogHeader.INT_ID = Citations.LogDetails.Paren
I have a trigger that updates my detail table. I would like to use the same trigger to also update the parent table based on a value in the detail table. I keep getting an error "The multi-part identifier "Citations.LogDetails.Pare
The trigger works when I use a literal numeric value but not when I pass a value. Is there a way to pass the value from the detail table in the first update statement to the update statement in the second table?
Thanks
/****** Object: Trigger [Citations].[tgr_LogDetail
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [Citations].[tgr_LogDetail
ON [Citations].[LogDetails]
AFTER UPDATE AS
UPDATE Citations.LogDetails
SET LastUpdatedOn = GETDATE()
WHERE LogDetailID IN (SELECT DISTINCT LogDetailID FROM Inserted)
UPDATE Citations.LogHeader
SET LogHeader.TotPlatesPerLog = (SELECT COUNT(DISTINCT(VehiclePlat
WHERE LogHeader.INT_ID = Citations.LogDetails.Paren
Last Comment
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
That SQL is very dangerous. Keep in mind that when you use an alias for a table name, the original table name no longer applies. Therefore, you should update the alias name instead. That is the only perfectly accurate way to do updates with a join:
update Header
set TotPlatesPerLog = Details.NoPlates
from LogHeader as Header
inner join (select ParentLogNo, count(distinct VehiclePlate) as NoPlates
from LogDetails
group by ParentLogNo) as Details on Header.INT_ID = Details.ParentLogNo
To better see how SQL doesn't even "know" what LogHeader is once an alias is used, try to run this query:
select LogHeader.INT_ID, Details.NoPlates
from LogHeader as Header
inner join (select ParentLogNo, count(distinct VehiclePlate) as NoPlates
from LogDetails
group by ParentLogNo) as Details on Header.INT_ID = Details.ParentLogNo
update Header
set TotPlatesPerLog = Details.NoPlates
from LogHeader as Header
inner join (select ParentLogNo, count(distinct VehiclePlate) as NoPlates
from LogDetails
group by ParentLogNo) as Details on Header.INT_ID = Details.ParentLogNo
To better see how SQL doesn't even "know" what LogHeader is once an alias is used, try to run this query:
select LogHeader.INT_ID, Details.NoPlates
from LogHeader as Header
inner join (select ParentLogNo, count(distinct VehiclePlate) as NoPlates
from LogDetails
group by ParentLogNo) as Details on Header.INT_ID = Details.ParentLogNo
Microsoft SQL Server
Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.
171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
ASKER