Help in update trigger

[techipa]

Hello All,

I have to write a update trigger on a table.
The table structure is some what like this

Study
Site (PK,FK)
Study (PK,FK)
VendorID

StudyPro
Site (PK,FK)
Study (PK,FK)
SeqNo (PK)
CustID
Protocol

Materials
CustID (PK)
Protocol (PK)
Material (PK)

The trigger should be on Update(CustID, Protocol) in StudyPro table

I have to insert the record from inserted in Materials table if the CustID and Protocol columns (StudyPro table) only for VendorID = 3 (Study table) are updated

And then delete the record from Material using deleted table only if that Protocol is not been used by any other study(StudyPro) where the VendorID = 3

I am confused on the deleted SQL. Can any one please help on how I should be handling this update trigger?

Any comments ot advice will be really appreciated

Thanks in advance

-techiPA

 

[nice95gle]

You should look at Cascading Referential Integrity in BOL.

That should help you with what you are trying to accomplish.

Well Done is better than well said
- Ben Franklin

 

[techipa]

Thanks Ben,

But the 'Cascading Referential Integrity' will not help me here because the
CustNo and Protocol columns are not the key columns in table StudyPro

 

[nice95gle]

I think it's Friday am my brain is not functioning to it's fullest.

I just read your post again and I am a little lost.

1. You need a update trigger on the StudyPro table.

2. Insert the record from Materials into... (this is where I'm lost). Now you mention VendorID = 3 from the study table (Lost again)

3. The delete part I understand.





Well Done is better than well said
- Ben Franklin

 

[techipa]

Hi Ben,


Insert a record from DB1.dbo.Materials to the ReplicatedDB.dbo.Materials table (which is replicated DB) only if the key columns on StudyPro and Study matches where Study.VendorID = 3

I have to insert or delete the row only if the record belongs to VendorID = 3 but the VendorID field is in different table so I need to join with that table

I delete the record from ReplicatedDB.dbo.Materials table only if the same Protocol is not been used by some other StudyPro.StudyID for VendorID = 3

I hope you would understand now.

Thanks,
-techiPA

 

[nice95gle]

Run these two queries and let me know if it gives you what you are looking for, then we'll take it from there.

For Insert

SELECT a.CustID, a.Protocol, a.Material
FROM 
(DB1.dbo.Materials a INNER JOIN DB1.dbo.StudyPro b
ON a.CustID = b.CustID)
INNER JOIN DB1.dbo.Study c 
ON (b.Study = c.Study) AND (b.Site = c.Site)
WHERE (c.VendorID)=3

For Delete

SELECT a.CustID, a.Protocol, a.Material
FROM 
(ReplicatedDB.dbo.Materials a LEFT JOIN ReplicatedDB.dbo.StudyPro b 
ON a.Protocol = b.Protocol) 
LEFT JOIN ReplicatedDB.dbo.Study c 
ON (b.Study = c.Study) AND (b.Site = c.Site)
WHERE c.Site Is Null
AND c.Study Is Null

Well Done is better than well said
- Ben Franklin

 

[techipa]

I do not get any records from both the queries.

 

[nice95gle]

Does the data in your tables have any records that meet the criteria for what you are looking for?

And did you get any errors when you ran the queries?

We'll get to the bottom of this.

Well Done is better than well said
- Ben Franklin

 

[techipa]

Hi Ben,

This is how I implemented so far.

The Materials table is the one where the insert should happen (which will be only for vendor =3) and
GMGlobal.dbo.Material table is in another table where all the records are present and I need to insert from this table to the Materials table

It does not work for delete.. It inserts the new row in the currentDB.dbo.Materials table from GMGlobal.dbo.Materials table

CREATE TRIGGER [uTrig_StudyPro] ON [dbo].[StudyPro]
FOR UPDATE
AS

BEGIN

IF UPDATE(CustNo) OR UPDATE(Protocol)
BEGIN
IF (SELECT COUNT(*) FROM Inserted
INNER JOIN Study SI
ON Inserted.Site = SI.Site
And Inserted.Study = SI.Study
WHERE SI.VendorID = 3) > 0

BEGIN
INSERT INTO Materials
(
CustomerID, Protocol, MaterialID, SiteID
)
SELECT GM.CustomerID, GM.Protocol, GM.MaterialID, GM.SiteID
FROM Inserted
INNER JOIN Deleted
ON Inserted.Site = Deleted.Site
And Inserted.Study = Deleted.Study
And Inserted.SeqNo = Deleted.SeqNo
INNER JOIN GMGlobal.dbo.Materials GM
ON GM.CustomerID = Inserted.CustNo
And GM.Protocol = Inserted.Protocol
INNER JOIN Study SI
ON SI.Site = Inserted.Site
And SI.Study = Inserted.Study
LEFT OUTER JOIN Materials FM
ON FM.CustomerID = GM.CustomerID
And FM.Protocol = GM.Protocol
And FM.MaterialID = GM.MaterialID
And FM.Site = GM.Site
WHERE SI.VendorID = 4 AND FM.MaterialID IS NULL

END

IF (SELECT COUNT(*) FROM Deleted INNER JOIN Study SI
ON deleted.Site = SI.Site
And deleted.Study = SI.Study
WHERE SI.VendorID = 4) > 0
BEGIN

IF (SELECT COUNT(*) FROM Deleted INNER JOIN StudyPro
-- ON (Deleted.CustNo = StudyPro.CustNo)
And (Deleted.Protocol = StudyPro.Protocol)
And (Deleted.StudyID <> StudyPro.StudyID) > 0


DELETE FROM FM
FROM Materials FM
INNER JOIN deleted
ON deleted.CustNo = FM.CustNo
And deleted.Protocol = FM.Protocol
-- And deleted.Site = FM.Site
INNER JOIN Inserted
ON Inserted.Site = deleted.Site
And Inserted.Study = deleted.Study
And Inserted.SeqNo = deleted.SeqNo
INNER JOIN Study SI
ON SI.Site = deleted.Site
And SI.Study = deleted.Study
-- LEFT OUTER JOIN
-- (SELECT Site, Study
-- FROM Study
-- INNER JOIN StudyPro
-- ON StudyPro.Site = Study.Site
-- And StudyPro.Study = Study.Study
-- WHERE Study.VendorID = 4
-- ) StuProtocols
-- ON StuPro.Site = deleted.Site
-- And StuPro.Study = deleted.Study -- WHERE StuProtocols.Study IS NULL
WHERE deleted.CustNo <> Inserted.CustNo
OR deleted.Protocol <> Inserted.Protocol


Thanks,
Amita

 

[nice95gle]

Sorry I didn't get a chance to really look at your code (meetings most of the day). I have to run again, but from what you are saying vs. what you are doing you are making this allot more difficult than it is.

All you are trying to do is delete any Materials that don’t have an active record in the Study table. The only way to get this info is via the Protocol. Really look at the query for the delete I posted, I believe this will get you started.

When I arrive home I will take a closer look at your code.


Well Done is better than well said
- Ben Franklin

 

[nice95gle]

Oh and just incase my query writing skills suck...LOL

My query for delete is just giving you any records in Materials table that don't have any corresponding records in Study table.


Well Done is better than well said
- Ben Franklin

 

[techipa]

Thanks for the reply Ben.

I will take a look at the query you posted earlier.

Thanks,
-techiPA