March 5, 2015 at 9:57 am
Hi, i'm new to sql server triggers.
I have 2 tables (one master and one slave) with a primary on multiple columns. I need to create a trigger on first table in order to syncronize second table.
In which way i could manage an update on multiple rows on a column on primary key?
My tables are:
CREATE TABLE [dbo].[Seasons](
[Entity] [nvarchar](4) NOT NULL,
[Code] [nvarchar](5) NOT NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_Seasons] PRIMARY KEY CLUSTERED
(
[Entity] ASC,
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Seasons2](
[Entity] [nvarchar](4) NOT NULL,
[Code] [nvarchar](5) NOT NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_Seasons2] PRIMARY KEY CLUSTERED
(
[Entity] ASC,
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [Seasons] VALUES ('XX','11','Test')
INSERT INTO [Seasons] VALUES ('XX','22','Test')
INSERT INTO [Seasons] VALUES ('XX','33','Test')
UPDATE [Seasons] SET Entity = 'YY'
In the worst case, could i iterate over inserted and deleted tables? These tables are in the same order?
Thank you very much!
March 5, 2015 at 10:19 am
Why do you need two tables with the same data? From a design perspective, this probably makes no sense.
If you are going to create code that inserts, updates, or deletes the Season table, why can't you add a second insert/update/delete to keep the Seasons2 table in sync?
Also, the choice of entity and code as the clustered primary key may not be the best choice.
You may want to consider adding a sequence or identity, making that the clustered PK, and creating a unique index on the entity and code fields.
As for a trigger, you need to be able to handle all three operations.
For an insert:
INSERT INTO Seasons2(Entity, Code, Description)
SELECT Entity, Code, Description
FROM INSERTED
For an update
UPDATE Seasons2
SET Seasons2.Entity = INSERTED.Entity
Seasons2.Code = INSERTED.Code
Seasons2.Description = INSERTED.Description
FROM Seasons2
INNER JOIN INSERTED ON <add key fields here>
For a delete
DELETE Seasons2
WHERE EXISTS (SELECT 1
FROM INSERTED WHERE Seasons2.Entity = INSERTED.Entity
Seasons2.Code = INSERTED.Code)
Lastly, IMHO, a trigger is a really poor place to be doing this type of work.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2015 at 3:02 am
Thank you for your response!
This tables are on two different databases, so we need to keep them in sync.
We cannot change tables structure because there are 3rd party applications that are using this table.
I post only one simple example, but we need to keep in sync 10 tables that haven't an IDENTITY column, but a primary key based on multiple column.
Your insert script is perfect!
I tried your DELETE Script, but it didn't work: i think that you would use DELETED table.
The correct script is this one:
CREATE TRIGGER TRG_Season_Delete
ON Season
AFTER DELETE AS
BEGIN
DELETE FROM Season
WHERE EXISTS (SELECT 1
FROM DELETED WHERE Entity = DELETED.Entity AND Code = DELETED.Code )
END
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 and DELETED tables and match records between these 2 table, but INSERTED and DELETED table are in the same order? For example third row of inserted table in the previous value of third row in DELETED table?
Thank you for your help, i hope that you have a better solution!
PS: your suggestions for database structure are very important, but i cannot change db, i have only to do sync triggers.
March 6, 2015 at 3:23 am
I tried your DELETE Script, but it didn't work: i think that you would use DELETED table.
My mistake!
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?
Also, these should not be after triggers. They should be insert, update and delete triggers.
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.
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.
There are two things I would try. If you have to do a trigger, only do an insert of the primary key into a holding table. Create a separate process that looks for unprocessed rows in this table, and then updates the rest of the tables. There will be some delay in the updates of the other tables.
Secondly, this seems like a perfect situation for transactional replication.
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!
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2015 at 4:05 am
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:
March 6, 2015 at 4:42 am
yes, one trigger for each operation. It is better to have only one trigger to manage all?
In this case, three triggers work best.
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.
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.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2015 at 4:55 am
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.
March 6, 2015 at 8:44 am
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
Some things to think about...
If this is a third-party app, will modifying the schema invalidate any support agreements?
And, obviously, test, test, test.
And then, test again, test again, test again.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2015 at 9:06 am
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 😉
March 6, 2015 at 9:18 am
Simplify this.
In the update trigger, delete the rows in the slave tables that match the deleted table, and insert the rows in the slave tables from the inserted trigger.
You never have to worry about updating, matching, etc. etc.
DELETE SlaveTable
WHERE EXISTS (SELECT 1 FROM Deleted WHERE Deleted.Entity = SlaveTable.Entity
AND Deleted.Code = SlaveTable.Code)
INSERT INTO SlaveTable (Entity, Code, Description)
SELECT Entity, Code, Description
FROM Inserted
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 6, 2015 at 9:21 am
Michael L John (3/6/2015)
Simplify this.In the update trigger, delete the rows in the slave tables that match the deleted table, and insert the rows in the slave tables from the inserted trigger.
You never have to worry about updating, matching, etc. etc.
DELETE SlaveTable
WHERE EXISTS (SELECT 1 FROM Deleted WHERE Deleted.Entity = SlaveTable.Entity
AND Deleted.Code = SlaveTable.Code)
INSERT INTO SlaveTable (Entity, Code, Description)
SELECT Entity, Code, Description
FROM Inserted
Do you think that it will work?
it seems too simple 🙂
March 6, 2015 at 9:27 am
Yes. This will work.
The simplest solution is usually the best solution.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply