March 9, 2017 at 2:34 pm
I've never done triggers before so I would appreciate it if someone could proof this from me.
I have two tables, one is the master list for equipment; the other is the history of locations. The master (bEMEM) keys are EMCo (company) and Equipment [number]. The table history table (vEMLocationHistory) keys are are EMCo (company) Equipment [number]number and Seq [number.] I've added udCondition to both tables. I would like that, whenever a new row is added to the history table it updates the appropriate equipment row.
Here is the code I have. How does it look?SET NOCOUNT ON
Go
CREATE TRIGGER trEMCondition_INSERT
ON vEMLocationHistory
FOR INSERT
AS
update e SET e.udCondition = i.udCondition
from dbo.bEMEM e
JOIN inserted i ON i.EMCo = e.EMCo AND i.Equipment = e.Equipment
Thank you for your help,
March 9, 2017 at 2:44 pm
Ken at work - Thursday, March 9, 2017 2:34 PMI've never done triggers before so I would appreciate it if someone could proof this from me.I have two tables, one is the master list for equipment; the other is the history of locations. The master (bEMEM) keys are EMCo (company) and Equipment [number]. The table history table (vEMLocationHistory) keys are are EMCo (company) Equipment [number]number and Seq [number.] I've added udCondition to both tables. I would like that, whenever a new row is added to the history table it updates the appropriate equipment row.
Here is the code I have. How does it look?
SET NOCOUNT ON
GoCREATE TRIGGER trEMCondition_INSERT
ON vEMLocationHistory
FOR INSERT
AS
update e SET e.udCondition = i.udCondition
from dbo.bEMEM e
JOIN inserted i ON i.EMCo = e.EMCo AND i.Equipment = e.EquipmentThank you for your help,
I have 2 comments offhand, I imagine you want the SET NOCOUNT ON to be inside the body of the trigger not outside the trigger?
Also, what should happen when the first time a new combination of EMCo and Equipment is entered into the vEMLocationHistory table? Right now it would be ignored.
March 9, 2017 at 2:58 pm
Chris Harshman - Thursday, March 9, 2017 2:44 PMI have 2 comments offhand, I imagine you want the SET NOCOUNT ON to be inside the body of the trigger not outside the trigger?Also, what should happen when the first time a new combination of EMCo and Equipment is entered into the vEMLocationHistory table? Right now it would be ignored.
I suppose I should ask 'do I want the NOCOUNT inside the body of the trigger'?
I don't understand why "the first time a new combination of EMCo and Equipment is entered into the vEMLocationHistory table" would be ignored? Isn't that an insert too? Just like any existing combination?
March 9, 2017 at 3:25 pm
Yes, the "SET NOCOUNT ON" should be w/i the trigger code itself:
CREATE TRIGGER trEMCondition_INSERT
ON dbo.vEMLocationHistory
AFTER INSERT
AS
SET NOCOUNT ON;
UPDATE e
SET e.udCondition = i.udCondition
FROM dbo.bEMEM e
INNER JOIN inserted i ON i.EMCo = e.EMCo AND i.Equipment = e.Equipment
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 10, 2017 at 6:47 am
Ken at work - Thursday, March 9, 2017 2:58 PMI don't understand why "the first time a new combination of EMCo and Equipment is entered into the vEMLocationHistory table" would be ignored? Isn't that an insert too? Just like any existing combination?
the problem as I saw it, is your trigger code only does an UPDATE to bEMEM table. I don't know if there's a foreign key constraint on the vEMLocationHistory that ensures that it cannot have a record with EMCo and Equipment combination that isn't already in bEMEM. If there is such a constraint, than just doing the UPDATE is fine. If there isn't a foreign key, then you should handle the case where it might not exist in bEMEM and do either the UPDATE or an INSERT.
March 10, 2017 at 11:12 am
Good point. I don't see it as a Primary/Foreign key combination. So there must be a constraint as I cannot add equipment to the history table that doesn't ready exist in the equipment table. I tested ScottPletcher's example and it worked fine.
Thank you everyone for your help,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply