Apply the following SQL Scripts with SQL Server Management Studio:
Script #1
/****** Object: Trigger [dbo].[ADG_TR_InvtDescrXref_Delete] Script Date: 08/30/2012 17:03:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[ADG_TR_InvtDescrXref_Delete] ON [dbo].[Inventory]
FOR DELETE
AS set nocount on
DECLARE @InvtID varchar(30)
DECLARE @Descr varchar(60)
DECLARE @RowsAffected int
SELECT @RowsAffected = @@ROWCOUNT
IF @RowsAffected = 1
BEGIN
SELECT @InvtID = InvtID, @Descr = Descr from DELETED
EXECUTE ADG_InvtDescrXref_Delete @InvtID, @Descr
END
IF @RowsAffected > 1
BEGIN
DECLARE DelCursor SCROLL CURSOR FOR SELECT InvtID, Descr FROM DELETED
OPEN DelCursor
FETCH FIRST FROM DelCursor INTO @InvtID, @Descr
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXECUTE ADG_InvtDescrXref_Delete @InvtID, @Descr
FETCH NEXT FROM DelCursor INTO @InvtID, @Descr
END
CLOSE DelCursor
DEALLOCATE DelCursor
END
-- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.
GO
Script #2
/****** Object: StoredProcedure [dbo].[ADG_InvtDescrXref_Populate] Script Date: 08/30/2012 17:22:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[ADG_InvtDescrXref_Populate]
AS set nocount on
DECLARE @InvtID varchar(30)
DECLARE @Descr varchar(60)
TRUNCATE TABLE InvtDescrXref
DECLARE InvCursor INSENSITIVE CURSOR FOR SELECT InvtID, Descr FROM Inventory
OPEN InvCursor
FETCH NEXT FROM InvCursor INTO @InvtID, @Descr
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXECUTE ADG_InvtDescrXref_Add @InvtID, @Descr
FETCH NEXT FROM InvCursor INTO @InvtID, @Descr
END
CLOSE InvCursor
DEALLOCATE InvCursor
-- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.
GO
Script #3
/****** Object: Trigger [dbo].[ADG_TR_InvtDescrXref_Add] Script Date: 08/30/2012 17:03:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER TRIGGER [dbo].[ADG_TR_InvtDescrXref_Add] ON [dbo].[Inventory]
FOR INSERT,UPDATE
AS
DECLARE @InvtID varchar(30)
DECLARE @Descr varchar(60)
DECLARE @TranStatusCode varchar(2)
DECLARE @RowsAffected int
DECLARE @OMInstalled smallint
SELECT @RowsAffected = @@ROWCOUNT
SET NOCOUNT ON
IF @RowsAffected = 1
BEGIN
IF UPDATE(Descr)
BEGIN
SELECT @InvtID = InvtID, @Descr = Descr from DELETED
EXECUTE ADG_InvtDescrXref_Delete @InvtID, @Descr
SELECT @InvtID = InvtID, @Descr = Descr from INSERTED
EXECUTE ADG_InvtDescrXref_Add @InvtID, @Descr
END
IF UPDATE(TranStatusCode)
BEGIN
Select @OMInstalled = count(*) from SOSetup (NOLOCK)
IF @OMInstalled > 0
BEGIN
SELECT @InvtID = InvtID, @TranStatusCode = TranStatusCode from INSERTED
EXECUTE ADG_ProcessMGr_PlnIn_CrtSh @InvtID, @TranStatusCode
END
END
END
IF @RowsAffected > 1
BEGIN
IF UPDATE(Descr)
BEGIN
DECLARE DelCursor SCROLL CURSOR FOR SELECT InvtID, Descr FROM DELETED
OPEN DelCursor
FETCH FIRST FROM DelCursor INTO @InvtID, @Descr
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXECUTE ADG_InvtDescrXref_Delete @InvtID, @Descr
FETCH NEXT FROM DelCursor INTO @InvtID, @Descr
END
CLOSE DelCursor
DEALLOCATE DelCursor
DECLARE InsCursor SCROLL CURSOR FOR SELECT InvtID, Descr FROM INSERTED
OPEN InsCursor
FETCH FIRST FROM InsCursor INTO @InvtID, @Descr
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXECUTE ADG_InvtDescrXref_Add @InvtID, @Descr
FETCH NEXT FROM InsCursor INTO @InvtID, @Descr
END
CLOSE InsCursor
DEALLOCATE InsCursor
END
IF UPDATE(TranStatusCode)
BEGIN
Select @OMInstalled = count(*) from SOSetup (NOLOCK)
IF @OMInstalled > 0
BEGIN
DECLARE InsCursor SCROLL CURSOR FOR SELECT InvtID, TranStatusCode FROM INSERTED
OPEN InsCursor
FETCH FIRST FROM InsCursor INTO @InvtID, @TranStatusCode
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXECUTE ADG_ProcessMgr_PlnIn_CrtSh @InvtID, @TranStatusCode
FETCH NEXT FROM InsCursor INTO @InvtID, @TranStatusCode
END
CLOSE InsCursor
DEALLOCATE InsCursor
END
END
END
-- Copyright 1998 by Advanced Distribution Group, Ltd. All rights reserved.
GO