renato.guiglia (3/6/2015)
First of all, thank you very much for your time! 😉Send Beer.
😀
I recommend a "holding" table. The triggers insert the old and new values into this table, a separate process then updates the other tables.
The holding table could be a solution, but in which way i match old value and new value beetween inserted and deleted table?
Inserted and deleted tables have the same order?
If it is true, i can do the pairing, in the other case i have to raise an error on update, checking trigger "columns_updated()" for PK fields.
A holding table will not work. There is no way to join inserted and deleted to get the before and after values. Unless I am missing something...
Let's go back 20 steps.
Is it possible to modify the code that updates this table? If this is a procedure, then that would be the place to do it.
ok. I think that the best thing is to modify DDL and add an Identity column to every source table (in destination table i remove identity) and modifiy PK to unique constraint.
This solves all of your problems.
UPDATE Slave
SET Slave.Entity = Master.Entity,
Slave.Code = Master.Code,
Slave.Description = Master.Description
FROM INSERTED I
INNER JOIN DatabaseName.dbo.Seasons2 Slave ON I.ID = Slave.ID
INNER JOIN DatabaseName.dbo.Seasons MASTER ON Slave.ID = Master.ID
No, there is a lot of code already written and we cannot modify.
Maybe i found a solution for update trigger (not in formal sql):
1- if @@ROWCOUNT = 1 I can update the record immediatly
2- @@ROWCOUNT > 1 and NOT COLUMNS_UPDATED(pk columns)
THEN Update usign your update script with join
3- @@ROWCOUNT > 1 and COLUMNS_UPDATED(pk columns)
THEN
Delete records in Season2 using deleted table for pk
Insert records in Season2 using inserted table
I don't like this solution, but i think that it will work also if a table has FK (an exception on constraint should be raised on master db, so the trigger don't have to manage it).
What do you think about it?
Thank you 😉