07-30-15, 06:29 #1Registered User
- Join Date
- Jun 2007
Unanswered: trigger for updating a value on one table when that value is updated on the base tabl
HI all couldn't get this to work
if the id1 will change in table1 it should also change the corresponding id1 field in table2 it does not do anything please help!!
CREATE TRIGGER [dbo].[IDCHANGE]
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE (ID1)
SET tb2.ID1 = 'I.id1'
FROM table2 tb2
JOIN deleted d ON tb2.id1=d.ID1 JOIN INSERTED i ON i.id1 = d.id1
WHERE tb2.ID1 = d.id1
07-30-15, 09:24 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
This is a "poster child" example for why schemas ought to be normalized.
Assuming that the id1 column is the PK (Primary Key) for both table1 and table2, I don't know of any way to do this because you can see all of the "new" and "old" values for the PK but can't determine which new and old values match each other. If you enforce single row updates (a nearly fatal mistake for performance), you can work around this problem.
-PatPIn theory, theory and practice are identical. In practice, theory and practice are unrelated.