Rewriting a Trigger. Ugh - SQL Server Forums

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

Rewriting a Trigger. Ugh

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-29 : 16:29:09

The table that this information is written to is:

/****** Object: Table [dbo].[soMods]   Script Date: 9/29/2008 3:26:00 PM ******/
CREATE TABLE [dbo].[soMods] (
[fenumber] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fsono] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[frelease] char(3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DateChanged] datetime NOT NULL,
[TriggerTable] char(10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[QuantityChanged] int NULL,
[PriceMod] numeric(17, 5) NOT NULL,
[ModType] char(1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[fProdCl] char(2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fPartno] char(25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[fGroup] char(6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FKSorelsID] int NOT NULL,
[identity_column] int IDENTITY(1, 1) NOT NULL)
ON [PRIMARY];
GO

/****** Object: Index [dbo].[FKSorelsID_ind]   Script Date: 9/29/2008 3:26:00 PM ******/
CREATE NONCLUSTERED INDEX [FKSorelsID_ind]
ON [dbo].[soMods]
([FKSorelsID])
WITH
FILLFACTOR = 100
ON [PRIMARY];
GO

Here comes the trigger:

CREATE TRIGGER SOMODAUDIT
   ON M2MData01.dbo.sorels
   AFTER UPDATE, INSERT, DELETE
AS
SET  NOCOUNT ON
DECLARE @Repair AS CHAR (10)

IF EXISTS
      (SELECT dbo.somast.fsono
         FROM dbo.somast
        WHERE somast.fstatus = 'Open'
          AND dbo.somast.fsono IN
                    (SELECT COALESCE (inserted.fsono, deleted.fsono)
                       FROM    inserted
                            FULL OUTER JOIN
                               deleted
                            ON inserted.identity_column =
                                  deleted.identity_column))
   BEGIN
      IF EXISTS (SELECT *
                   FROM inserted)
     AND EXISTS (SELECT *
                   FROM deleted) -- Update
         BEGIN
            INSERT CHELTONCUSTOMIZATIONS.DBO.SOMODS
                   (fenumber,
                    fsono,
                    frelease,
                    DateChanged,
                    TriggerTable,
                    QuantityChanged,
                    PriceMod,
                    ModType,
                    fProdCl,
                    fPartno,
                    fGroup,
                    FKSorelsID)
               SELECT I.FENUMBER,
                      I.FSONO,
                      I.FRELEASE,
                      GETDATE (),
                      'SORELS',
                      (I.forderqty - D.forderqty),
                      ( ( (I.forderqty - I.finvqty
                         - (CASE
                               WHEN ( (I.fshipbook + I.fshipbuy + I.fshipmake)
                                   - I.finvqty) <= 0
                                 OR COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (I.fsono + I.finumber + I.frelease),
                                              0
                                    ) <= 0
                               THEN
                                  0
                               WHEN (I.fshipbook + I.fshipbuy + I.fshipmake)
                                  - I.finvqty <
                                       COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(I.fsono
                                                                            + I.finumber
                                                                            + I.frelease),
                                                 0
                                       )
                               THEN
                                  (I.fshipbook
                                 + I.fshipbuy
                                 + I.fshipmake
                                 - I.finvqty)
                               ELSE
                                  COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (I.fsono + I.finumber + I.frelease),
                                            0
                                  )
                            END))
                       * I.funetprice)
                     - ( (D.forderqty - D.finvqty
                        - (CASE
                              WHEN ( (D.fshipbook + D.fshipbuy + D.fshipmake)
                                  - D.finvqty) <= 0
                                OR COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (D.fsono + D.finumber + D.frelease),
                                             0
                                   ) <= 0
                              THEN
                                 0
                              WHEN (D.fshipbook + D.fshipbuy + D.fshipmake)
                                 - D.finvqty <
                                      COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY(D.fsono
                                                                           + D.finumber
                                                                           + D.frelease),
                                                0
                                      )
                              THEN
                                 (D.fshipbook
                                + D.fshipbuy
                                + D.fshipmake
                                - D.finvqty)
                              ELSE
                                 COALESCE (DBO.GETSHIPPEDNOTINVOICEDQTY (D.fsono + D.finumber + D.frelease),
                                           0
                                 )
                           END))
                      * D.funetprice)),
                      CASE
                         WHEN EXISTS
                                 (SELECT dbo.somast.fsono
                                    FROM dbo.somast
                                   WHERE somast.fusercode = 'Repair'
                                     AND dbo.somast.fsono IN
                                               (SELECT COALESCE (inserted.fsono,
                                                                 deleted.fsono
                                                       )
                                                  FROM    inserted
                                                       FULL OUTER JOIN
                                                          deleted
                                                       ON inserted.identity_column =
                                                             deleted.identity_column))
                         THEN
                            'R'
                         ELSE
                            'C'
                      END,
                      SOI.fprodcl,
                      SOI.fPartno,
                      SOI.fGroup,
                      I.identity_column
                 FROM       INSERTED I
                         INNER JOIN
                            DELETED D
                         ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
                      INNER JOIN
                         [M2MData01].[dbo].SOITEM SOI
                      ON I.FINUMBER = SOI.FINUMBER AND I.FSONO = SOI.FSONO
                WHERE I.FUNETPRICE <> D.FUNETPRICE
                   OR I.FORDERQTY <> D.FORDERQTY

            RETURN
         END
      IF NOT EXISTS (SELECT *
                       FROM inserted)
     AND EXISTS (SELECT *
                   FROM deleted) -- Delete
         BEGIN
            INSERT CHELTONCUSTOMIZATIONS.DBO.SOMODS
                   (fenumber,
                    fsono,
                    frelease,
                    DateChanged,
                    TriggerTable,
                    QuantityChanged,
                    PriceMod,
                    ModType,
                    fProdCl,
                    fPartno,
                    fGroup,
                    FKSorelsID)
               SELECT D.FENUMBER,
                      D.FSONO,
                      D.FRELEASE,
                      GETDATE (),
                      'SORELS',
                      (0 - D.forderqty),
                      (0 - D.FNETPRICE) AS PRICEMOD,
                      CASE
                         WHEN EXISTS
                                 (SELECT dbo.somast.fsono
                                    FROM dbo.somast
                                   WHERE somast.fusercode = 'Repair'
                                     AND dbo.somast.fsono IN
                                               (SELECT COALESCE (inserted.fsono,
                                                                 deleted.fsono
                                                       )
                                                  FROM    inserted
                                                       FULL OUTER JOIN
                                                          deleted
                                                       ON inserted.identity_column =
                                                             deleted.identity_column))
                         THEN
                            'R'
                         ELSE
                            'D'
                      END,
                      SOI.fProdcl,
                      SOI.fPartno,
                      SOI.fGroup,
                      D.IDENTITY_column
                 FROM    DELETED D
                      INNER JOIN
                         [M2MData01].[dbo].SOITEM SOI
                      ON D.FINUMBER = SOI.FINUMBER AND D.FSONO = SOI.FSONO

            RETURN
         END
      IF EXISTS (SELECT *
                   FROM inserted)
     AND NOT EXISTS (SELECT *
                       FROM deleted) -- Insert
         BEGIN
            INSERT CHELTONCUSTOMIZATIONS.DBO.SOMODS
                   (fenumber,
                    fsono,
                    frelease,
                    DateChanged,
                    TriggerTable,
                    QuantityChanged,
                    PriceMod,
                    ModType,
                    fProdCl,
                    fPartno,
                    fGroup,
                    FKSorelsID)
               SELECT I.FENUMBER,
                      I.FSONO,
                      I.FRELEASE,
                      GETDATE (),
                      'SORELS',
                      I.FORDERQTY,
                      I.FNETPRICE,
                      CASE
                         WHEN EXISTS
                                 (SELECT dbo.somast.fsono
                                    FROM dbo.somast
                                   WHERE somast.fusercode = 'Repair'
                                     AND dbo.somast.fsono IN
                                               (SELECT COALESCE (inserted.fsono,
                                                                 deleted.fsono
                                                       )
                                                  FROM    inserted
                                                       FULL OUTER JOIN
                                                          deleted
                                                       ON inserted.identity_column =
                                                             deleted.identity_column))
                         THEN
                            'R'
                         ELSE
                            'N'
                      END,
											'xx',
											'xx',
											'xx',
                      I.identity_column
                 FROM INSERTED I
            RETURN
         END
   END
GO

The updates work, deletes work, inserts (highlighted) don't.

I've been messing around and trying to figure out what's wrong for about 2 hours. The error thrown in SQL Profiler is

Error: 207, Severity: 16, State: 1

Can anyone help me?

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-09-29 : 18:21:41

One thing I noticed is that in your
INSERT CHELTONCUSTOMIZATIONS.DBO.SOMODS staement
you are inserting 9 fields in to 12

Jim

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-29 : 23:57:54

quote:
Originally posted by jimf

One thing I noticed is that in your
INSERT CHELTONCUSTOMIZATIONS.DBO.SOMODS staement
you are inserting 9 fields in to 12

Jim

nope its correct. think you missed the last three 'x' values

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-30 : 10:28:44

I originally wrote the insert portion without those three fields as they will accept nulls. In an attempt to diagnose the problem I wrote bogus info to them.

Anybody have any idea what's wrong here?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 11:13:07

quote:
Originally posted by DavidChel

I originally wrote the insert portion without those three fields as they will accept nulls. In an attempt to diagnose the problem I wrote bogus info to them.

Anybody have any idea what's wrong here?

is this only error info you get? any messages along with it?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-09-30 : 11:17:22

My idea (but not sure):

quote:

IF EXISTS (SELECT *
FROM inserted)
AND NOT EXISTS (SELECT *
FROM deleted) -- Insert

That means "no update and no delete" was taken.
In that case i can imagine that the "TABLE" deleted has NO STRUCTURE.
But you're using "deleted" in your JOIN.
However it's not a good idea to join deleted when nothing is deleted...

Excuse me if i'm wrong.

Greetings
Webfred

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 11:21:14

quote:
Originally posted by webfred

My idea (but not sure):
quote:

IF EXISTS (SELECT *
FROM inserted)
AND NOT EXISTS (SELECT *
FROM deleted) -- Insert

That means "no update and no delete" was taken.
In that case i can imagine that the "TABLE" deleted has NO STRUCTURE.
But you're using "deleted" in your JOIN.
However it's not a good idea to join deleted when nothing is deleted...

Excuse me if i'm wrong.

Greetings
Webfred

Planning replaces chance by mistake

Sorry Webfred...what you said doesnt make sense. The above query just uses two subqueries, it doesnt join with deleted table.What it checks is if records exists in inserted and not anything exists in deleted which is perfectly fine for insert scenario.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-09-30 : 11:25:00

quote:

FROM inserted
FULL OUTER JOIN
deleted
ON inserted.identity_column =
deleted.identity_column

Planning replaces chance by mistake

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-30 : 11:28:26

quote:
Originally posted by webfred

quote:

FROM inserted
FULL OUTER JOIN
deleted
ON inserted.identity_column =
deleted.identity_column

Planning replaces chance by mistake

even this wont cause any probelms as its a FULL OUTER JOIN though deleted has no records, it will still fetch values from inserted table and COALESCE will return non null value correctly from inserted.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-09-30 : 12:57:57

Error: 207, Severity: 16, State: 1 means invalid column name.

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

Subscribe to my blog

james_wells
Yak Posting Veteran

55 Posts

Posted - 2008-09-30 : 15:42:04

just a thought as you code appears fine at first glance

is it possible that the trigger does not insert records
as somast.fstatus is not = 'Open' when the update trigger is
executed for new records in M2MData01.dbo.sorels and gets set to 'open' at a later date / time.

Seen something like this before with an account status that starts
off as "new" when the main account has been inserted/created and only gets set to "open" when transactions have been inserted/posted to the account

---------------

IF EXISTS
(SELECT dbo.somast.fsono
FROM dbo.somast
WHERE somast.fstatus = 'Open'
AND dbo.somast.fsono IN
(SELECT COALESCE (inserted.fsono, deleted.fsono)
FROM inserted
FULL OUTER JOIN
deleted
ON inserted.identity_column =
deleted.identity_column))

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-09-30 : 17:24:32

I'm sorry, but I wasted everyones' time.

This trigger is working fine. I realized that when my ERP system does an update, it updates 3 different tables. All 3 have triggers. I had not yet updated the 3rd table's trigger.

That was the trigger that was failing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources