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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER