First of all, thank you very much for your time! 😉
yes, one trigger for each operation. It is better to have only one trigger to manage all?
In this case, three triggers work best.
OK
This is the problem: if i UPDATE a field in the primary key (for example entity from 'XX' to 'YY'), i cannot join inserted and deleted tables on primary key, because values are different and don't match. You example work perfectly if you update only "description" field because the join works.
You are modifying the values of a primary key? This is a very bad practice. If this is something provided by the third party, then I would start looking for another third party.
If this is something that is being decided by the business, then that needs to be revisited.
You will not be able to do this in a trigger using a join. A loop is even more problematic.
How do you plan on identifying the rows that need to be updated if you are changing the key values?
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.
Secondly, this seems like a perfect situation for transactional replication.
I know, but i didn't take this choice :crying:
Transactional replication will not work because you are modifying the PK.
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.