USE [Total_Database]
GO
/****** Object:  Trigger [dbo].[Item_insupd]    Script Date: 12-10-2013 18:02:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Item_insupd] ON [dbo].[Item] FOR INSERT, UPDATE AS
SET NOCOUNT ON
DECLARE @ItemNo varchar(20), @ItemDescription varchar(40), @TaxCode varchar(1)
IF NOT (EXISTS (SELECT T.ItemTypeID FROM ItemType T INNER JOIN Inserted I ON T.ItemTypeID = I.ItemTypeID))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
Item Type does not exist', 16, 1, @ItemNo, @ItemDescription)
ROLLBACK TRANSACTION
END
DECLARE @CostingMethod smallint, @Inventory bit, @StandardCost money
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription, @StandardCost = I.StandardCost, @Inventory = T.Inventory, @CostingMethod = T.CostingMethod FROM Inserted I INNER JOIN ItemType T ON T.ItemTypeID = I.ItemTypeID
IF @StandardCost IS NULL AND @Inventory = 1 AND @CostingMethod = 2
BEGIN
RAISERROR ('Unable to insert or update item ''%s: %s''...
Standard Cost must have a value if Item Type costing method is standard', 16, 1, @ItemNo, @ItemDescription)
ROLLBACK TRANSACTION
END
IF NOT (EXISTS (SELECT G.GLAccountID FROM GLAccnt G INNER JOIN Inserted I ON G.GLAccountID = I.SalesGLAccountID))
OR ((SELECT I.COGSGLAccountID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.GLAccountID FROM GLAccnt G INNER JOIN Inserted I ON G.GLAccountID = I.COGSGLAccountID))
OR ((SELECT I.InventoryGLAccountID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.GLAccountID FROM GLAccnt G INNER JOIN Inserted I ON G.GLAccountID = I.InventoryGLAccountID))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
GL Account does not exist', 16, 1, @ItemNo, @ItemDescription)
ROLLBACK TRANSACTION
END
IF ((SELECT I.SalesSubAccount1ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub1 G INNER JOIN Inserted I ON G.SubAccountID = I.SalesSubAccount1ID))
OR ((SELECT I.COGSSubAccount1ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub1 G INNER JOIN Inserted I ON G.SubAccountID = I.COGSSubAccount1ID))
OR ((SELECT I.InventorySubAccount1ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub1 G INNER JOIN Inserted I ON G.SubAccountID = I.InventorySubAccount1ID))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
Subaccount 1 does not exist', 16, 1, @ItemNo, @ItemDescription)
ROLLBACK TRANSACTION
END
IF ((SELECT I.SalesSubAccount2ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub2 G INNER JOIN Inserted I ON G.SubAccountID = I.SalesSubAccount2ID))
OR ((SELECT I.COGSSubAccount2ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub2 G INNER JOIN Inserted I ON G.SubAccountID = I.COGSSubAccount2ID))
OR ((SELECT I.InventorySubAccount2ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub2 G INNER JOIN Inserted I ON G.SubAccountID = I.InventorySubAccount2ID))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
Subaccount 2 does not exist', 16, 1, @ItemNo, @ItemDescription)
ROLLBACK TRANSACTION
END
IF ((SELECT I.SalesSubAccount3ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub3 G INNER JOIN Inserted I ON G.SubAccountID = I.SalesSubAccount3ID))
OR ((SELECT I.COGSSubAccount3ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub3 G INNER JOIN Inserted I ON G.SubAccountID = I.COGSSubAccount3ID))
OR ((SELECT I.InventorySubAccount3ID FROM Inserted I) IS NOT NULL) AND NOT (EXISTS (SELECT G.SubAccountID FROM GLAcSub3 G INNER JOIN Inserted I ON G.SubAccountID = I.InventorySubAccount3ID))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
Subaccount 3 does not exist', 16, 1, @ItemNo, @ItemDescription)
ROLLBACK TRANSACTION
END
IF ((SELECT SUBSTRING(I.TaxCodes,1,1) FROM Inserted I) <> '') AND NOT (EXISTS (SELECT T.TaxCode FROM Tax T INNER JOIN Inserted I ON T.TaxCode = SUBSTRING(I.TaxCodes,1,1)))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription, @TaxCode = SUBSTRING(I.TaxCodes,1,1) FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
Tax Code ''%s'' does not exist on any taxes', 16, 1, @ItemNo, @ItemDescription, @TaxCode)
ROLLBACK TRANSACTION
END
IF ((SELECT SUBSTRING(I.TaxCodes,2,1) FROM Inserted I) <> '') AND NOT (EXISTS (SELECT T.TaxCode FROM Tax T INNER JOIN Inserted I ON T.TaxCode = SUBSTRING(I.TaxCodes,2,1)))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription, @TaxCode = SUBSTRING(I.TaxCodes,2,1) FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
Tax Code ''%s'' does not exist on any taxes', 16, 1, @ItemNo, @ItemDescription, @TaxCode)
ROLLBACK TRANSACTION
END
IF ((SELECT SUBSTRING(I.TaxCodes,3,1) FROM Inserted I) <> '') AND NOT (EXISTS (SELECT T.TaxCode FROM Tax T INNER JOIN Inserted I ON T.TaxCode = SUBSTRING(I.TaxCodes,3,1)))
BEGIN
SELECT @ItemNo = I.ItemNo, @ItemDescription = I.ItemDescription, @TaxCode = SUBSTRING(I.TaxCodes,3,1) FROM Inserted I
RAISERROR ('Unable to insert or update item ''%s: %s''...
Tax Code ''%s'' does not exist on any taxes', 16, 1, @ItemNo, @ItemDescription, @TaxCode)
ROLLBACK TRANSACTION
END