How did I write to the other server's database? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How did I write to the other server's database?

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-24 : 16:07:59

So, I have a trigger on a table in my main ERP system.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- Add the new Trigger for SoMast

CREATE TRIGGER SOMODAUDITSTATUS
   ON M2MData01.dbo.somast
   AFTER UPDATE
AS
SET  NOCOUNT ON
DECLARE @PRICE AS MONEY
DECLARE @SONO AS CHAR (6)

IF EXISTS (SELECT inserted.fstatus, deleted.fstatus
             FROM    inserted
                  INNER JOIN
                     deleted
                  ON inserted.identity_column = deleted.identity_column
            WHERE inserted.fstatus = 'Open' AND deleted.fstatus <> 'Open') -- Order moves TO open.  Add $$
   BEGIN
      INSERT CHELTONCUSTOMIZATIONS.DBO.SOMODS
             (FENUMBER,
              FSONO,
              FRELEASE,
              FDATECHANGED,
              [fstatusOLD],
              [fstatusNEW],
              FORDERQTYOLD,
              FORDERQTYNEW,
              FUNETPRICEOLD,
              FUNETPRICENEW,
              PRICEMOD,
              MODTYPE,
              FKSorelsID,
              fProdCl,
              fPartno,
              fGroup)
         SELECT dbo.sorels.FENUMBER,
                dbo.sorels.FSONO,
                dbo.sorels.FRELEASE,
                GETDATE (),
                deleted.fstatus,
                inserted.fstatus,
                dbo.sorels.forderqty,
                dbo.sorels.FORDERQTY,
                dbo.sorels.FUNETPRICE,
                dbo.sorels.FUNETPRICE,
                ( (SORELS.forderqty - SORELS.finvqty
                 - (CASE
                       WHEN ( (SORELS.fshipbook
                             + SORELS.fshipbuy
                             + sorels.fshipmake)
                           - sorels.finvqty) <= 0
                         OR COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (sorels.fsono + sorels.finumber + sorels.frelease),
                                      0
                            ) <= 0
                       THEN
                          0
                       WHEN (SORELS.fshipbook
                           + SORELS.fshipbuy
                           + sorels.fshipmake)
                          - sorels.finvqty <
                               COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                                                    + sorels.finumber
                                                                    + sorels.frelease),
                                         0
                               )
                       THEN
                          (SORELS.fshipbook
                         + SORELS.fshipbuy
                         + sorels.fshipmake
                         - sorels.finvqty)
                       ELSE
                          COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (sorels.fsono + sorels.finumber + sorels.frelease),
                                    0
                          )
                    END))
               * SORELS.funetprice),
                (CASE
                    WHEN inserted.fstatus = 'Open'
                     AND deleted.fstatus = 'Started'
                    THEN
                       'N'
                    ELSE
                       'C'
                 END),
                dbo.sorels.identity_column,
                dbo.soitem.fprodcl,
                dbo.soitem.fpartno,
                dbo.soitem.fgroup
           FROM             dbo.sorels
                         INNER JOIN
                            DBO.somast
                         ON DBO.somast.fsono = dbo.sorels.fsono
                      INNER JOIN
                         dbo.soitem
                      ON dbo.soitem.fsono = dbo.sorels.fsono
                     AND dbo.soitem.finumber = dbo.sorels.finumber
                   INNER JOIN
                      INSERTED
                   ON DBO.Somast.identity_column = INSERTED.IDENTITY_COLUMN
                INNER JOIN
                   deleted
                ON INSERTED.IDENTITY_COLUMN = deleted.IDENTITY_COLUMN

      RETURN
   END
IF EXISTS (SELECT inserted.fstatus, deleted.fstatus
             FROM    inserted
                  INNER JOIN
                     deleted
                  ON inserted.identity_column = deleted.identity_column
            WHERE DELETED.fstatus = 'Open' AND INSERTED.fstatus <> 'Open') -- Order moves FROM open.  Subtract $$
   BEGIN
      INSERT CHELTONCUSTOMIZATIONS.DBO.SOMODS
             (FENUMBER,
              FSONO,
              FRELEASE,
              FDATECHANGED,
              [fstatusOLD],
              [fstatusNEW],
              FORDERQTYOLD,
              FORDERQTYNEW,
              FUNETPRICEOLD,
              FUNETPRICENEW,
              PRICEMOD,
              MODTYPE,
              FKSorelsID,
              fProdCl,
              fPartno,
              fGroup)
         SELECT dbo.sorels.FENUMBER,
                dbo.sorels.FSONO,
                dbo.sorels.FRELEASE,
                GETDATE (),
                deleted.fstatus,
                inserted.fstatus,
                dbo.sorels.FORDERQTY,
                dbo.sorels.FORDERQTY,
                dbo.sorels.FUNETPRICE,
                dbo.sorels.FUNETPRICE,
                ( (SORELS.forderqty - SORELS.finvqty
                 - (CASE WHEN ( (SORELS.fshipbook
                             + SORELS.fshipbuy
                             + sorels.fshipmake)
                           - sorels.finvqty) <= 0
                         OR COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (sorels.fsono + sorels.finumber + sorels.frelease),
                                      0
                            ) <= 0
                       THEN
                          0
                       WHEN (SORELS.fshipbook
                           + SORELS.fshipbuy
                           + sorels.fshipmake)
                          - sorels.finvqty <
                               COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(sorels.fsono
                                                                    + sorels.finumber
                                                                    + sorels.frelease),
                                         0
                               )
                       THEN
                          (SORELS.fshipbook
                         + SORELS.fshipbuy
                         + sorels.fshipmake
                         - sorels.finvqty)
                       ELSE
                          COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (sorels.fsono + sorels.finumber + sorels.frelease),
                                    0
                          )
                    END))
               * SORELS.funetprice
               * -1),
                'C',
                dbo.sorels.identity_column,
                dbo.soitem.fprodcl,
                dbo.soitem.fpartno,
                dbo.soitem.fgroup
           FROM             dbo.sorels
                         INNER JOIN
                            DBO.somast
                         ON DBO.somast.fsono = dbo.sorels.fsono
                      INNER JOIN
                         dbo.soitem
                      ON dbo.soitem.fsono = dbo.sorels.fsono
                     AND dbo.soitem.finumber = dbo.sorels.finumber
                   INNER JOIN
                      INSERTED
                   ON DBO.Somast.identity_column = INSERTED.IDENTITY_COLUMN
                INNER JOIN
                   deleted
                ON INSERTED.IDENTITY_COLUMN = deleted.IDENTITY_COLUMN

      RETURN
   END
SET @SONO = (SELECT    TOP 1
                    (FSONO)
               FROM INSERTED)

IF EXISTS (SELECT somast.fsono
             FROM SOMAST
            WHERE SOMAST.fusercode = 'Repair' AND FSONO = @SONO)
   BEGIN
      UPDATE CHELTONCUSTOMIZATIONS.DBO.SOMODS
         SET ModType = 'R'
       WHERE fsono = @SONO
   END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

I have another server which I use for testing and development. I commonly use the production backups to refresh my databases on my test server.

Today I went to test something on my test box and I noticed that my triggers were not righting to the corresponding on my test server, but rather to the table on my live server. This was of course a revelation.

Can someone suggest how I managed to committ this blunder?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-24 : 16:38:50

Is CHELTONCUSTOMIZATIONS.DBO.SOMODS your live system? If not, what are the names of each to make it easier to look through your code.

Are the two systems on separate SQL Server instances?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-24 : 16:41:20

quote:
Originally posted by tkizer

Is CHELTONCUSTOMIZATIONS.DBO.SOMODS your live system? If not, what are the names of each to make it easier to look through your code.

Are the two systems on separate SQL Server instances?

CHELTONCUSTOMIZATIONS.DBO.SOMODS is the database.owner.table and it is the same name on both systems.

Yes, the two systems are on different servers and therefore SQL Server Instances if I'm understanding you correctly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-24 : 16:42:50

Your trigger didn't update the wrong system then. You must be mistaken as there is no linked server in use in your code and therefore it can't get to another instance.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources