Solved

SQL Query - Trigger for Insert/Update/Delete with condition

Posted on 2010-08-17
6
426 Views
Last Modified: 2012-06-27
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'.

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

Open in new window

0
Comment
Question by:holemania
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 2

Expert Comment

by:Eric3141
ID: 33456154
Try this.  You'll likely want to put the DECLARE at the top of the trigger:


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
0
 
LVL 55

Expert Comment

by:Huseyin KAHRAMAN
ID: 33456158
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
0
 
LVL 55

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 33456173
sorry wrong part, add condition to db3 part

--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
WHERE PURCHASED = 'Y'
END
0
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
LVL 2

Accepted Solution

by:
Eric3141 earned 250 total points
ID: 33456195
Ack... you said you wanted to IGNORE if purchased = 'Y'.  Revised code below:

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
0
 

Author Comment

by:holemania
ID: 33459283
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.
0
 
LVL 2

Expert Comment

by:Eric3141
ID: 33460766
You're welcome.  :)
0

Featured Post

Quiz: What Do These Organizations Have In Common?

Hint: Their teams ended up taking quizzes, too.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question