Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2010-08-17
6
Medium Priority
?
430 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 61

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 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Accepted Solution

by:
Eric3141 earned 1000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

571 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