Solved

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

Posted on 2010-08-17
6
424 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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
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 our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

808 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