I tried your DELETE Script, but it didn't work: i think that you would use DELETED table.
My mistake!
No problem 😉
Your UPDATE script is correct ONLY if you update description field.
If you run the query that i posted:
UPDATE [Seasons] SET Entity = 'YY'
The trigger will update 0 rows instead of 3 rows.
The only idea that i have is to iterate over INSERTED
No, something is wrong.
Have you created three separate triggers, an insert, update and delete?
yes, one trigger for each operation. It is better to have only one trigger to manage all?
Also, these should not be after triggers. They should be insert, update and delete triggers.
I create them with FOR.
Stop thinking in rows, and start thinking in columns. There is no reason to "iterate" over these rows.
The inserted and deleted tables are copies of the table that has been modified. If 10 rows get modified, then inserted will have copies of the 10 new rows, and deleted will have copies of the 10 old rows.
You only need to join to inserted or deleted on the primary keys.
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.
Also, if I understand this correctly, you are updating 10 tables in 10 different databases?
Are these on the same server, or are they linked servers?
If this is true, then this is NOT a good idea to do in a trigger. You will be creating a situation that will likely be a source of deadlocking and blocking.
I have 10 tables and 2 different database (master and slave) on the same server. The goal is to keep in sync tables from master to slave.
Secondly, this seems like a perfect situation for transactional replication.
I know, but i didn't take this choice :crying:
Please do some research on the positives and negatives of triggers. They certainly have their uses, but they are probably one of the most misused features in a relational database. Just because you can, doesn't mean you should!
Unfortunately, i have to use triggers :crying: