Solved

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

Posted on 2010-08-17
6
416 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
  • 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 51

Expert Comment

by:HainKurt
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 51

Assisted Solution

by:HainKurt
HainKurt 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now