October 25, 2018 at 12:45 pm
I have a database with normalized tables and another application with non-normalized table.
So I need to create a trigger for when I insert / update a register in the source table (normalized)
I can insert some of these records into a table in the destination (non-normalized).
I want to ask colleagues for help if there is any better way to do this. Or if I'm on the right track.
Initially I created a trigger in which I test if there is any update, otherwise I do INSERT in the destination table (non normalized)
CREATE TRIGGER [dbo].[TriggerUpdateInsert] ON [dbo].[TABLE_SOURCE]
AFTER INSERT, UPDATE
AS
DECLARE @QtdRows INT
BEGIN
UPDATE [BANK_TANK]..[TABLE_DESTINATION]
SET FIELD_1 = TABLE_SOURCE.FIELD_1
WHERE
TABLE_SOURCE.FIELD_KEY = TABLE_DESTINATION.FIELD_KEY
SET @QtdRows = @@ROWCOUNT
UPDATE [BANCO_TANK]..
SET FIELD_2 = TABLE_SOURCE.FIELD_2
WHERE
TABELA_SOURCE.FIELD_KEY = TABLE_DESTINATION.FIELD_KEY
SET @QtdRows = @@ROWCOUNT
-- if UPDATE did not find one register, I INSERT new register in table destination.
IF ( @QtdRows = 0 )
BEGIN
INSERT INTO [BANCO_TANK]..[TABLE_DESTINATION]
SELECT
FIELD_1,
FIELD_2,
FROM TABLE_SOURCE1
LEFT JOIN TABLE_SOURCE2 ON
TABLE_SOURCE1.FIELD_KEY = TABLE_SOURCE2.FIELD_KEY
END
Thank you
October 25, 2018 at 2:02 pm
What about DELETEs?
Any chance of just using a View in database BANCO_TANK that points to TABLE_SOURCE?
October 25, 2018 at 2:50 pm
C DBA Lockhart - Thursday, October 25, 2018 2:02 PMWhat about DELETEs?
Any chance of just using a View in database BANCO_TANK that points to TABLE_SOURCE?
Lockhart
Thanks for your feedback, but there is already an old application database. Where this data is worked locally as a BI. So I think in this case the view is not a good idea. about delete is not necessary.
If you have another idea please share again!
Thank very much
October 25, 2018 at 3:59 pm
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[TriggerUpdateInsert]
ON [dbo].[TABLE_SOURCE]
AFTER INSERT, UPDATE
AS
SET NOCOUNT ON;
UPDATE TD
SET FIELD_1 = i.FIELD_1,
FIELD_2 = i.FIELD_2
FROM [BANK_TANK].dbo.[TABLE_DESTINATION] TD
INNER JOIN inserted i ON i.FIELD_KEY = TD.FIELD_KEY
-- INSERT new register row in table_destination if one doesn't already exist.
INSERT INTO [BANCO_TANK].dbo.[TABLE_DESTINATION] ( FIELD_KEY, FIELD_1, FIELD_2 )
SELECT i.FIELD_KEY, i.FIELD_1, i.FIELD_2
FROM inserted i
WHERE NOT EXISTS (
SELECT 1
FROM [BANCO_TANK].dbo.[TABLE_DESTINATION] TD
WHERE TD.FIELD_KEY = i.FIELD_KEY
)
/* end of trigger */
GO
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 26, 2018 at 11:49 am
Guru Thank you for feedback,
Thank you for your feedback. I liked. But an observation in this case, if I have three fields and update only one, the other two will be overwritten.
For this case I do
-- If There is Update Field_1
IF UPDATE (Field_1)
BEGIN
UPDATE [BANK_DESTINATION]..[TABLE_Destination]
SET
TD.[Field_1] = I.[Field_1]
FROM [BANK_DESTINATION]..TABLE_Destination TD
INNER JOIN INSERTED I ON
TD.[Field.Key] = I.[Field.Key]
END
SET @qtdeRows = @@rowcount
-- If There is Update Field_2
IF UPDATE (Field_1)
BEGIN
UPDATE [BANK_DESTINATION]..[TABLE_Destination]
SET
TD.[Field_1] = I.[Field_1]
FROM [BANK_DESTINATION]..TABLE_Destination TD
INNER JOIN INSERTED I ON
TD.[Field.Key] = I.[Field.Key]
END
SET @qtdeRows = @@rowcount
IF (@qtdeRows = 0 )
BEGIN
INSERT INTO [TANK_FEDB]..[MWIN_TANK100_PARTS]
What your opinion ?
Thank you.
October 26, 2018 at 12:04 pm
antoniop.silv - Friday, October 26, 2018 11:49 AMGuru Thank you for feedback,
Thank you for your feedback. I liked. But an observation in this case, if I have three fields and update only one, the other two will be overwritten.
For this case I do-- If There is Update Field_1
IF UPDATE (Field_1)
BEGIN
UPDATE [BANK_DESTINATION]..[TABLE_Destination]
SET
TD.[Field_1] = I.[Field_1]
FROM [BANK_DESTINATION]..TABLE_Destination TD
INNER JOIN INSERTED I ON
TD.[Field.Key] = I.[Field.Key]
END
SET @qtdeRows = @@rowcount-- If There is Update Field_2
IF UPDATE (Field_1)
BEGIN
UPDATE [BANK_DESTINATION]..[TABLE_Destination]
SET
TD.[Field_1] = I.[Field_1]
FROM [BANK_DESTINATION]..TABLE_Destination TD
INNER JOIN INSERTED I ON
TD.[Field.Key] = I.[Field.Key]
END
SET @qtdeRows = @@rowcountIF (@qtdeRows = 0 )
BEGIN
INSERT INTO [TANK_FEDB]..[MWIN_TANK100_PARTS]What your opinion ?
Thank you.
-- If There is Update Field_1 or Field_2
IF UPDATE(Field_1) OR UPDATE(Field_2)
BEGIN
UPDATE [BANK_DESTINATION]..[TABLE_Destination]
SET
TD.[Field_1] = CASE WHEN UPDATE(Field_1) THEN I.[Field_1] ELSE TD.[Field_1] END,
TD.[Field_2] = CASE WHEN UPDATE(Field_2) THEN I.[Field_2] ELSE TD.[Field_2] END
FROM [BANK_DESTINATION]..TABLE_Destination TD
INNER JOIN INSERTED I ON
TD.[Field.Key] = I.[Field.Key]
END /*IF*/
SET @qtdeRows = @@rowcount
...
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 26, 2018 at 1:02 pm
Thank you my friend.
You are the guy.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply