/* This is the Sorels trigger which keeps track of changes made to sales orders
which are in the open state.  The trigger on Somast tracks value changes when 
changing states.  */

IF EXISTS ( SELECT  *
            FROM    dbo.sysobjects
            WHERE   id = OBJECT_ID(N'[dbo].[SOMODAUDIT]')
                    AND OBJECTPROPERTY(id, N'IsTrigger') = 1 ) 
    DROP TRIGGER [dbo].[SOMODAUDIT]
GO

CREATE TRIGGER SOMODAUDIT ON M2MData01.dbo.sorels
    AFTER UPDATE, INSERT, DELETE
AS
    SET NOCOUNT ON
    DECLARE @PRICE AS MONEY
    IF EXISTS ( SELECT  somast.fstatus
                FROM    dbo.somast,
                        inserted,
                        deleted
                WHERE   ( dbo.somast.fsono = inserted.fsono
                          OR dbo.somast.fsono = deleted.fsono)
                        AND somast.fstatus = 'Open' ) 
        BEGIN
            IF EXISTS ( SELECT  *
                        FROM    inserted )
                AND EXISTS ( SELECT *
                             FROM   deleted ) -- Update
                BEGIN
                    INSERT  CHELTONCUSTOMIZATIONS.DBO.SOMODS
                            (
                              FENUMBER,
                              FSONO,
                              FRELEASE,
                              FDATECHANGED,
                              fstatusOLD,
                              fstatusNEW,
                              FORDERQTYOLD,
                              FORDERQTYNEW,
                              FUNETPRICEOLD,
                              FUNETPRICENEW,
                              PRICEMOD,
                              MODTYPE,
                              FKSorelsID
                            )
                            SELECT  I.FENUMBER,
                                    I.FSONO,
                                    I.FRELEASE,
                                    GETDATE(),
                                    'Open',
                                    'Open',
                                    D.FORDERQTY,
                                    I.FORDERQTY,
                                    D.FUNETPRICE,
                                    I.FUNETPRICE,
                                    ( ( INSERTED.forderqty - INSERTED.finvqty
                                        - ( CASE WHEN ( ( INSERTED.fshipbook
                                                          + INSERTED.fshipbuy
                                                          + INSERTED.fshipmake )
                                                        - INSERTED.finvqty ) <= 0
                                                      OR COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(INSERTED.fsono + INSERTED.finumber + INSERTED.frelease), 0) <= 0
                                                 THEN 0
                                                 WHEN ( INSERTED.fshipbook
                                                        + INSERTED.fshipbuy
                                                        + INSERTED.fshipmake )
                                                      - INSERTED.finvqty < COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(INSERTED.fsono + INSERTED.finumber + INSERTED.frelease), 0)
                                                 THEN ( INSERTED.fshipbook
                                                        + INSERTED.fshipbuy
                                                        + INSERTED.fshipmake
                                                        - INSERTED.finvqty )
                                                 ELSE COALESCE(DBO.GETSHIPPEDNOTINVOICEDQTY(INSERTED.fsono + INSERTED.finumber + INSERTED.frelease), 0)
                                            END ) ) * INSERTED.funetprice ),
                                    'C',
                                    I.identity_column
                            FROM    INSERTED I
                                    INNER JOIN DELETED D ON I.IDENTITY_COLUMN = D.IDENTITY_COLUMN
                            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,
                              FDATECHANGED,
                              fstatusOLD,
                              fstatusNEW,
                              FORDERQTYOLD,
                              FUNETPRICEOLD,
                              PRICEMOD,
                              MODTYPE,
                              FKSorelsID
                            )
                            SELECT  D.FENUMBER,
                                    D.FSONO,
                                    D.FRELEASE,
                                    GETDATE(),
                                    'Open',
                                    'Open',
                                    D.FORDERQTY,
                                    D.FUNETPRICE,
                                    ( 0 - D.FNETPRICE ) AS PRICEMOD,
                                    'D',
                                    d.IDENTITY_column
                            FROM    DELETED D

                    RETURN		
                END

            IF EXISTS ( SELECT  *
                        FROM    inserted )
                AND NOT EXISTS ( SELECT *
                                 FROM   deleted ) -- Insert
                BEGIN
                    INSERT  CHELTONCUSTOMIZATIONS.DBO.SOMODS
                            (
                              FENUMBER,
                              FSONO,
                              FRELEASE,
                              FDATECHANGED,
                              fstatusOLD,
                              fstatusNEW,
                              FORDERQTYNEW,
                              FUNETPRICENEW,
                              PRICEMOD,
                              MODTYPE,
                              FKSorelsID
                            )
                            SELECT  I.FENUMBER,
                                    I.FSONO,
                                    I.FRELEASE,
                                    GETDATE(),
                                    'Open',
                                    'Open',
                                    I.FORDERQTY,
                                    I.FUNETPRICE,
                                    ( I.FNETPRICE ) AS PRICEMOD,
                                    'N',
                                    I.identity_column
                            FROM    INSERTED I

                    RETURN    
                END 
        END                    
GO

Server: Msg 107, Level 16, State 2, Procedure SOMODAUDIT, Line 21
The column prefix 'INSERTED' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure SOMODAUDIT, Line 21
The column prefix 'INSERTED' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure SOMODAUDIT, Line 21
The column prefix 'INSERTED' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Procedure SOMODAUDIT, Line 21
The column prefix 'INSERTED' does not match with a table name or alias name used in the query.

CREATE TRIGGER SOMODAUDIT ON M2MData01.dbo.sorels
    AFTER UPDATE, INSERT, DELETE
AS
    SET NOCOUNT ON
    DECLARE @PRICE AS MONEY
    IF EXISTS ( SELECT  dbo.somast.fsono
                FROM    dbo.somast
                WHERE   somast.fstatus = 'Open' AND dbo.somast.fsono = COALESCE(inserted.fsono, deleted.fsono))