Hi Gurus!
I have a wierd problem. I am having a web application developed in ASP.NET, the database is SQL Server Express 2005. I have written a trigger which updates another table's column value when a record is inserted or update.
The two tables are
InvtVendorInvoices (The trigger is on this table, trigger name is OnAlter_VendorInvoices)
InvtMaster (This table's coloumn "invtAmtOthers" value gets updated from the trigger on InvtVendorInvoices)
When I am inserting or updating a record from SQL Server Management Studio Express in InvtVendorInvoices table, the "invtAmtOthers" coloumn value in the InvtMaster table is being updated successfully. However, when I insert or update a record from an .aspx web page the trigger doesn't fires. The interesting thing is that the record which tried to inserted from the webpage gets inserted or update in the InvtVendorInvoices table but the trigger doesn't fire and the "invtAmtOther" coloumn in InvtMaster table is left unaltered.
I have done lot of search on the internet but couldn't find the solution.
Please help me out of this mess...
Any help will be highly appreciated.
Database Tables I have are...
USE [Stone]
GO
/****** Object: Table [dbo].[InvtMaster] Script Date: 12/10/2009 22:19:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InvtMaster](
[invtID] [int] IDENTITY(1,1) NOT NULL,
[invtDate] [datetime] NULL,
[invtSupID] [int] NULL,
[invtRefNo] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[invtRefDate] [datetime] NULL,
[invtPOID] [int] NULL,
[invtCurID] [int] NULL,
[invtPayTermsID] [int] NULL,
[invtLocationID] [int] NULL,
[invtNotesPrinted] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[invtNotesInternal] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[invtAmtMaterial] [money] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtAmtTotal] DEFAULT ((0)),
[invtAmtFreight] [money] NULL,
[invtAmtOthers] [money] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtAmtOthers] DEFAULT ((0)),
[invtStatusID] [int] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtStatusID] DEFAULT ((0)),
[invtWflStatusID] [int] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtWflStatusID] DEFAULT ((0)),
[invtOwnerID] [int] NULL,
CONSTRAINT [PK_InvtMaster] PRIMARY KEY CLUSTERED
(
[invtID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
USE [Stone]
GO
/****** Object: Table [dbo].[InvtMaster] Script Date: 12/10/2009 22:23:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InvtMaster](
[invtID] [int] IDENTITY(1,1) NOT NULL,
[invtDate] [datetime] NULL,
[invtSupID] [int] NULL,
[invtRefNo] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[invtRefDate] [datetime] NULL,
[invtPOID] [int] NULL,
[invtCurID] [int] NULL,
[invtPayTermsID] [int] NULL,
[invtLocationID] [int] NULL,
[invtNotesPrinted] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[invtNotesInternal] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[invtAmtMaterial] [money] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtAmtTotal] DEFAULT ((0)),
[invtAmtFreight] [money] NULL,
[invtAmtOthers] [money] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtAmtOthers] DEFAULT ((0)),
[invtStatusID] [int] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtStatusID] DEFAULT ((0)),
[invtWflStatusID] [int] NULL CONSTRAINT [DF_MaterialInventoryMaster_invtWflStatusID] DEFAULT ((0)),
[invtOwnerID] [int] NULL,
CONSTRAINT [PK_InvtMaster] PRIMARY KEY CLUSTERED
(
[invtID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Trigger I have...
USE [Stone]
GO
/****** Object: Trigger [dbo].[OnAlter_VendorInvoice] Script Date: 12/10/2009 22:25:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Farooq
-- Create date: 10-Dec-09
-- Description: When the Inventory Invoice Is Created, the total amout for all the additional cost is calculated and updated in the invtAmtOthers field in the InvtMaster Table
-- =============================================
CREATE TRIGGER .[dbo].[OnAlter_VendorInvoice]
ON .[dbo].[InvtVendorInvoices]
AFTER INSERT,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 @invtID int;
declare @value float;
select @invtID=invInvtID from inserted;
--The ISNULL is used to convert the NULL returned value to ZERO
select @value=ISNULL(sum(invAmtTotal),0) from InvtVendorInvoices where invInvtID=@invtID;
--select @value=sum(invAmtTotal) from InvtVendorInvoices where invInvtID=@invtID;
update InvtMaster set invtAmtOthers=@value where invtID=@invtID;
END
The ASPX web page has the following insert record code on a button click event
Dim cmd As New SqlCommand
cmd.CommandText = "INSERT INTO InvtVendorInvoices (invSupID,invInvtID,invRefNo,invDesc,invDate,invDateDue,invAmtTotal,invNotesInternal) VALUES (@invSupID,@invInvtID,@invRefNo,@invDesc,@invDate,@invDateDue,@invAmtTotal,@invNotesInternal)"
cmd.Connection = DataHelper.GetConnection
cmd.Parameters.Add("@invSupID", SqlDbType.Int).Value = e.NewValues("invSupID")
cmd.Parameters.Add("@invInvtID", SqlDbType.Int).Value = lblInvtID.Text
cmd.Parameters.Add("@invRefNo", SqlDbType.NVarChar).Value = e.NewValues("invRefNo")
cmd.Parameters.Add("@invDesc", SqlDbType.NText).Value = e.NewValues("invDesc")
cmd.Parameters.Add("@invDate", SqlDbType.DateTime).Value = e.NewValues("invDate")
cmd.Parameters.Add("@invDateDue", SqlDbType.DateTime).Value = e.NewValues("invDateDue")
cmd.Parameters.Add("@invAmtTotal", SqlDbType.Money).Value = e.NewValues("invAmtTotal")
cmd.Parameters.Add("@invNotesInternal", SqlDbType.NText).Value = e.NewValues("invNotesInternal")
cmd.Connection.Open()
cmd.ExecuteNonQuery()