October 29, 2018 at 9:25 am
I have replication setup to replicate several tables between database a & database b. When a record is inserted in one of the tables I want to execute a trigger to insert some of the newly inserted records details into another table (it has an identity column and I use this ID in several places throughout the system - I cant change this) Are there any best practices I should follow under these circumstances ? I've never been the greatest fan of triggers but I cant see any other way to achieve what I'm after. This is what I've got so far, are there any pitfalls in this approach ?
CREATE TRIGGER AlignmentTrigger ON party
FOR INSERT
AS
DECLARE @id varchar(32)
SELECT @id = [partyid]
FROM INSERTED
If Not Exists(select id from Organisation where id = @id)
Begin
INSERT INTO Organisation( Name, id,Status )
SELECT party.name, party.partyid,party.status
FROM party
WHERE party.partyid = @id
End
GO
October 29, 2018 at 10:32 am
I've actually had a rethink about this and decided to use a stored procedure activated through a job every 5 minutes, I don't need 'real time' updates for the particular table, every 5 mins is fine and its independent of the replication process.
October 30, 2018 at 7:58 am
solus - Monday, October 29, 2018 9:25 AMI have replication setup to replicate several tables between database a & database b. When a record is inserted in one of the tables I want to execute a trigger to insert some of the newly inserted records details into another table (it has an identity column and I use this ID in several places throughout the system - I cant change this) Are there any best practices I should follow under these circumstances ? I've never been the greatest fan of triggers but I cant see any other way to achieve what I'm after. This is what I've got so far, are there any pitfalls in this approach ?
CREATE TRIGGER AlignmentTrigger ON party
FOR INSERT
AS
DECLARE @id varchar(32)
SELECT @id = [partyid]
FROM INSERTEDIf Not Exists(select id from Organisation where id = @id)
Begin
INSERT INTO Organisation( Name, id,Status )
SELECT party.name, party.partyid,party.status
FROM party
WHERE party.partyid = @id
End
GO
Just an FYI, perhaps, but maybe an important one. A trigger has to be able to handle ANY number of rows being inserted. Your code appears to assume that there will only ever be one row, and that's the kind of assumption that can seriously mess you up. Triggers fire once per insert, not once per inserted row.
EDIT: spelling
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply