holemania
asked on
SQL Query - Trigger for Insert/Update/Delete with condition
Hello experts,
I have 3 databases that are identical. When a user add in something to DB1, I want it to replicate to the other 2 databases. The current trigger works, but I want to add in a condition. If the item is a purchased part, I don't want it to replicate to DB3. See code below. It is working as current, except it doesn't ignore replicate to DB3 if purchased = 'Y'.
I have 3 databases that are identical. When a user add in something to DB1, I want it to replicate to the other 2 databases. The current trigger works, but I want to add in a condition. If the item is a purchased part, I don't want it to replicate to DB3. See code below. It is working as current, except it doesn't ignore replicate to DB3 if purchased = 'Y'.
CREATE TRIGGER [INSERT_UPDATE_DELETE] ON [dbo].[PART]
FOR INSERT, UPDATE, DELETE
AS
DECLARE @D INT, @I INT
SELECT @D = COUNT(*) FROM DELETED
SELECT @I = COUNT(*) FROM INSERTED
IF @I = 0
BEGIN
DELETE FROM DB2.DBO.PART WHERE ID IN (SELECT ID FROM DELETED)
DELETE FROM DB3.DBO.PART WHERE ID IN (SELECT ID FROM DELETED)
END
--DATABASE 2
IF @D = 0
BEGIN
INSERT INTO DB2.DBO.PART(
ID, DESCRIPTION, PURCHASED, FABRICATED
)
SELECT ID, DESCRIPTION, PURCHASED, FABRICATED
FROM INSERTED
END
IF @D > 0 AND @I > 0
BEGIN
UPDATE DB2.DBO.PART
SET DESCRIPTION = I.DESCRIPTION,
PURCHASED = I.PURCHASED,
FABRICATED = I.FABRICATED
FROM DB2.DBO.PART P INNER JOIN INSERTED I ON P.ID = I.ID
--DATABASE 3, WILL ONLY INSERT IF PURCHASED IS SET 'Y'
IF @D = 0
BEGIN
INSERT INTO DB3.DBO.PART(
ID, DESCRIPTION, PURCHASED, FABRICATED
)
SELECT ID, DESCRIPTION, PURCHASED, FABRICATED
FROM INSERTED
END
IF @D > 0 AND @I > 0
BEGIN
UPDATE DB3.DBO.PART
SET DESCRIPTION = I.DESCRIPTION,
PURCHASED = I.PURCHASED,
FABRICATED = I.FABRICATED
FROM DB3.DBO.PART P INNER JOIN INSERTED I ON P.ID = I.ID
END
add a where condition to select part
--DATABASE 2
IF @D = 0
BEGIN
INSERT INTO DB2.DBO.PART(
ID, DESCRIPTION, PURCHASED, FABRICATED
)
SELECT ID, DESCRIPTION, PURCHASED, FABRICATED
FROM INSERTED
WHERE PURCHASED = 'Y'
END
--DATABASE 2
IF @D = 0
BEGIN
INSERT INTO DB2.DBO.PART(
ID, DESCRIPTION, PURCHASED, FABRICATED
)
SELECT ID, DESCRIPTION, PURCHASED, FABRICATED
FROM INSERTED
WHERE PURCHASED = 'Y'
END
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you. My bad with the typo in regard to the purchase. It's basically the same thing but it's fabricated items that I don't want to insert into DB3. Only insert if Purchased items = "Y". The example worked!!! Thanks again.
You're welcome. :)
DECLARE
@Purchased char(1);
--DATABASE 3, WILL ONLY INSERT IF PURCHASED IS SET 'Y'
SELECT @Purchased = Purchased FROM INSERTED;
IF @D = 0 AND @Purchased = 'Y'
BEGIN
INSERT INTO DB3.DBO.PART(
ID,
DESCRIPTION,
PURCHASED,
FABRICATED)
SELECT
ID,
DESCRIPTION,
PURCHASED,
FABRICATED
FROM INSERTED
END