bad_boy_2007
شنبه 18 اسفند 1386, 17:27 عصر
سلام دوستان من یک مشکل کوچیک با تریگرهای نوع Update دارم
من 2 تا جدول دارم یکی جدول OrderDetail و دیگری جدول Product جدول OrderDetail مقادیر را به ازای فاکتور فروش در خودش ذخیره میکنه و هر بار که سفارش جدیدی به این جدول اضافه ، حذف یا ویرایش بشه موجودی کالا (Stock) تو جدول مربوط به کالا ها (Product) بروز رسانی میشه تا اینجاش مشکلی ندارم و همونطور که میخوام کار میکنه .
مشکل من اینجاست که میخوام فیلد موجودی (Stock) از جدول کالاها(Product) بطور مستقیم قابل ویرایش نباشه و فقط توسط اون تریگرهایی که تو جدول سفارشات (OrderDetail) وجود داره قابل ویرایش باشه واسه این کار کد زیر رو نوشتم :
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER dbo.NotAllowModifyStock
ON dbo.Product
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if update(stock) rollback
-- Insert statements for trigger here
END
GO
و هین شد مشکل من !!
یعنی میخوام یه استثنا واسه کد بالا در نظر بگیرم که اگه این تغییر از طرف تریگرهای جدول OrderDetail بود ، کار ویرایش موجودی فعلی کالا انجام بشه .
دیتابیسم رو ضمیمه کردم این هم اسکریپت ایجاد کردن دیتابیس :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[stock] [float] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[NotAllowModifyStock]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[NotAllowModifyStock]
ON [dbo].[Product]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if update(stock) rollback
-- Insert statements for trigger here
END
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderDetail]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OrderDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[Quantity] [float] NOT NULL,
[ProductID] [int] NOT NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[OrderDecreaseFromProductsStocDelete]'))
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author: Hosein
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[OrderDecreaseFromProductsStocDelete]
ON [dbo].[OrderDetail]
AFTER delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @temp float
set @temp =(select stock from deleted inner join product on product.id=deleted.productid)
set @temp = @temp + (select quantity from deleted)
update product set stock=@temp where id=(Select Productid from deleted)
END
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[OrderDecreaseFromProductsStock]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[OrderDecreaseFromProductsStock]
ON [dbo].[OrderDetail]
AFTER insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @tempStock float
set @tempStock = (select stock from product inner join inserted on product.id=inserted.ProductID)
set @tempStock = @tempStock - (select Quantity from inserted)
update product set stock=@tempStock where id=(select productID from inserted)
END
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[OrderDecreaseFromProductsStockUpdate]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: Hosein
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[OrderDecreaseFromProductsStockUpdate]
ON [dbo].[OrderDetail]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @temp float
set @temp= (select quantity from deleted)
set @temp= @temp - (select quantity from inserted)
set @temp= (select stock from product where id=(select productid from deleted)) - @temp
update product set stock=@temp where id=(select productid from deleted)
END
'
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderDetail_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetail]'))
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ID])
GO
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Product]
من 2 تا جدول دارم یکی جدول OrderDetail و دیگری جدول Product جدول OrderDetail مقادیر را به ازای فاکتور فروش در خودش ذخیره میکنه و هر بار که سفارش جدیدی به این جدول اضافه ، حذف یا ویرایش بشه موجودی کالا (Stock) تو جدول مربوط به کالا ها (Product) بروز رسانی میشه تا اینجاش مشکلی ندارم و همونطور که میخوام کار میکنه .
مشکل من اینجاست که میخوام فیلد موجودی (Stock) از جدول کالاها(Product) بطور مستقیم قابل ویرایش نباشه و فقط توسط اون تریگرهایی که تو جدول سفارشات (OrderDetail) وجود داره قابل ویرایش باشه واسه این کار کد زیر رو نوشتم :
-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER dbo.NotAllowModifyStock
ON dbo.Product
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if update(stock) rollback
-- Insert statements for trigger here
END
GO
و هین شد مشکل من !!
یعنی میخوام یه استثنا واسه کد بالا در نظر بگیرم که اگه این تغییر از طرف تریگرهای جدول OrderDetail بود ، کار ویرایش موجودی فعلی کالا انجام بشه .
دیتابیسم رو ضمیمه کردم این هم اسکریپت ایجاد کردن دیتابیس :
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Product]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[stock] [float] NOT NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[NotAllowModifyStock]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[NotAllowModifyStock]
ON [dbo].[Product]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
if update(stock) rollback
-- Insert statements for trigger here
END
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderDetail]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OrderDetail](
[ID] [int] IDENTITY(1,1) NOT NULL,
[OrderID] [int] NOT NULL,
[Quantity] [float] NOT NULL,
[ProductID] [int] NOT NULL,
CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[OrderDecreaseFromProductsStocDelete]'))
EXEC dbo.sp_executesql @statement = N'
-- =============================================
-- Author: Hosein
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[OrderDecreaseFromProductsStocDelete]
ON [dbo].[OrderDetail]
AFTER delete
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @temp float
set @temp =(select stock from deleted inner join product on product.id=deleted.productid)
set @temp = @temp + (select quantity from deleted)
update product set stock=@temp where id=(Select Productid from deleted)
END
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[OrderDecreaseFromProductsStock]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: Name
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[OrderDecreaseFromProductsStock]
ON [dbo].[OrderDetail]
AFTER insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @tempStock float
set @tempStock = (select stock from product inner join inserted on product.id=inserted.ProductID)
set @tempStock = @tempStock - (select Quantity from inserted)
update product set stock=@tempStock where id=(select productID from inserted)
END
'
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[OrderDecreaseFromProductsStockUpdate]'))
EXEC dbo.sp_executesql @statement = N'-- =============================================
-- Author: Hosein
-- Create date:
-- Description:
-- =============================================
CREATE TRIGGER [dbo].[OrderDecreaseFromProductsStockUpdate]
ON [dbo].[OrderDetail]
AFTER UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @temp float
set @temp= (select quantity from deleted)
set @temp= @temp - (select quantity from inserted)
set @temp= (select stock from product where id=(select productid from deleted)) - @temp
update product set stock=@temp where id=(select productid from deleted)
END
'
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_OrderDetail_Product]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetail]'))
ALTER TABLE [dbo].[OrderDetail] WITH CHECK ADD CONSTRAINT [FK_OrderDetail_Product] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Product] ([ID])
GO
ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Product]