Solved

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

Posted on 2010-08-17
6
418 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

863 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

23 Experts available now in Live!

Get 1:1 Help Now