Advertisement

06.09.2008 at 07:48PM PDT, ID: 23471139
[x]
Attachment Details

Help with possible infinite looping triggers

Asked by ChristinaPupo in MS SQL Server, Web Servers, WebApplications

Tags: mssql server

Hi,

I have 2 store databases (retail & wholesale), each database contains the same tables and structure but most with different data. There are several tables that will contain the same information such as products, productOptions, categories, etc.

I have two seperate web apps for updating each database. Each page contains several forms for updating the various tables and submits to an execute page that carries out the updates.

I have created (with help from expert SIMONET) a trigger that will update/insert/delete the wholesale database after the retail one has been updated thru the web app.

Everything works great until I try to create the same type of triggers for the wholesale database (to perform the updates on the retail database).

I think I have created a infinite loop of triggers. I am not really sure because the error is:

 [DBNETLIB][ConnectionRead (recv()).]General network error. Check your network documentation.

But it seems logical that the trigger are acting on each other.

Can anyone help to solve this situation? Here is the trigger code:Start Free Trial 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:

USE [jcpascu_retailDEV]
GO
 
create trigger [jcpascu_admin].[tr_products]
   on [jcpascu_admin].[products]
   after Insert,Update,Delete
as
 
begin
  
   declare @InsertedCount int
   declare @DeletedCount int
   set @InsertedCount = (select count(*) from inserted)
   set @DeletedCount = (select count(*) from deleted)
 
   if @InsertedCount > 0 begin
      -- first do updated rows
      if @DeletedCount > 0 begin
         update [jcpascu_wholesaleDEV].[jcpascu_admin].[products]
           set price = A.price,
           noShipCharge = A.noShipCharge,
           fileName = A.fileName,
           homepage = A.homepage,
           smallImageUrl = A.smallImageUrl,
           listPrice = A.listPrice,
           sku = A.sku,
           relatedKeys = A.relatedKeys,
           details = A.details,
           descriptionLong = A.descriptionLong,
           description = A.description,
           imageUrl = A.imageUrl,
           reviewAllow = A.reviewAllow,
           stock = A.stock,
           weight = A.weight,
           reviewAutoActive = A.reviewAutoActive,
           refurbished = A.refurbished,
           msrp = A.msrp,
           handlingfee = A.handlingfee,
           AltImageURL = A.AltImageURL,
           Freight = A.Freight,
           active = A.active,
           supplier = A.supplier,
           sortOrder = A.sortOrder,
           taxExempt = A.taxExempt,
           UPC = A.UPC,
           minqty = A.minqty,
           idBrand = A.idBrand,
           hotDeal = A.hotDeal,
           idManufacturer = A.idManufacturer,
           mpn = A.mpn,
           wholesaleprice = A.wholesaleprice,
           map = A.map,
           estimatedship = A.estimatedship,
           XLrgImageURL = A.XLrgImageURL
         FROM Inserted A
         WHERE A.idProduct in (select [idProduct] from deleted) and ([jcpascu_wholesaleDEV].[jcpascu_admin].[products].idProduct = A.idProduct)
      end
      else
      begin
          -- now do inserted rows
          SET IDENTITY_INSERT [jcpascu_wholesaleDEV].[jcpascu_admin].[products] ON
          INSERT INTO [jcpascu_wholesaleDEV].[jcpascu_admin].[products]
           ([idProduct],price,noShipCharge,fileName,homepage,
           smallImageUrl,listPrice,sku,relatedKeys,
           details,descriptionLong,description,imageUrl,
           reviewAllow,stock,weight,reviewAutoActive,
           refurbished,msrp,handlingfee,AltImageURL,Freight,
           active,supplier,sortOrder,taxExempt,UPC,minqty,
           idBrand,hotDeal,idManufacturer,mpn,
           wholesaleprice,map,estimatedship,XLrgImageURL)
           select [idProduct],price,noShipCharge,fileName,homepage,
           smallImageUrl,listPrice,sku,relatedKeys,
           details,descriptionLong,description,imageUrl,
           reviewAllow,stock,weight,reviewAutoActive,
           refurbished,msrp,handlingfee,AltImageURL,Freight,
           active,supplier,sortOrder,taxExempt,UPC,minqty,
           idBrand,hotDeal,idManufacturer,mpn,
           wholesaleprice,map,estimatedship,XLrgImageURL
          FROM Inserted
          SET IDENTITY_INSERT [jcpascu_wholesaleDEV].[jcpascu_admin].[products] OFF
      end
 end
    else
          -- now do deleted rows
          if @DeletedCount > 0  begin
            DELETE FROM [jcpascu_wholesaleDEV].[jcpascu_admin].[products] 
            WHERE idProduct IN (SELECT idProduct FROM deleted)
          end
end
 
Keywords: Help with possible infinite looping triggers
Title
1 Infinite Universe?
2 window.location causing an infinite loop
3 [DBNETLIB][ConnectionRead (rec…
4 infinite firing of triggers
5 Infinite loop in triggers, on update
6 Infinite Loop
 
Loading Advertisement...
 
[+][-]06.10.2008 at 01:34AM PDT, ID: 21749352

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 7-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]06.11.2008 at 01:24AM PDT, ID: 21757837

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 7-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]06.12.2008 at 02:21AM PDT, ID: 21767450

View this solution now by starting your 7-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

 

About this solution

Zones: MS SQL Server, Web Servers, WebApplications
Tags: mssql server
Sign Up Now!
Solution Provided By: sqlconsumer
Participating Experts: 1
Solution Grade: A
 
 
 
Loading Advertisement...
20080716-EE-VQP-32 / EE_QW_2_20070628