May 20, 2008 at 3:19 am
Hi all,
I have been asked to look into a problem where a specific table is not updating.
The table is in a database called infocarenotts-test
But i guess you cvan gather by the code.
USE [infocarenotts-test]
GO
/****** Object: Table [dbo].[ProcessedData] Script Date: 05/20/2008 10:09:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ProcessedData](
[HistoryID] [int] NOT NULL,
[RetroFlag] [bit] NOT NULL,
CONSTRAINT [PK_ProcessedData] PRIMARY KEY CLUSTERED
(
[HistoryID] ASC,
[RetroFlag] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Now when I opwn the table and insert data.
I enter a history ID and when tabbed to the next column i get a explanation mark
saying "The cell has changed, the change has not been commited to the database, the original data is null"
I then enter a "true" into the RetroFlag and then get an explanation error saying
"This row was successfully comitted to the database
However a problem occured when attempting to retrieve the data back after the commit. Because of this, the displayed data in this row is read-only.
To fix this problem, please re-run the query."
When querying the table there are no entries.
If i use T-SQL
INSERT INTO [infocarenotts-test].[dbo].[ProcessedData]
([HistoryID]
,[RetroFlag])
VALUES (1111, 1)
This runs fine with no errors but again I am unable to view any data in the table.
Idealy i would like to enter 1 or 0 values (true or false)
But when doing this.
I get invalid value for Row 1 column 2
the changed value in this cell was not recorgnised as valid
.net framework data type boolean.
string not recognised as a valid bollean.
press escape to cancel the change.
I am a newby in this enviroment and just been thrown into this task.
The code is not mine and have just been asked to look into this.
Why is this not commiting to the database?
Is there a problem with the code?
Many thanks for any help.
May 20, 2008 at 4:00 am
I cannot reproduce the problem using SQL Server 2005 Management Studio version 9.0.3042, which is service pack 2.
What is the version of Management Studio ?
SQL = Scarcely Qualifies as a Language
May 20, 2008 at 5:00 am
Our server is running 9.00.3054.00SP2Standard Edition
like you, when i create the same table on my stand alone machine everything works fine. My version is 9.00.3042.00SP2Standard Edition
Could this be a permissions issue?
Though I am logged in as sys admin.
Thanks
May 20, 2008 at 5:19 am
Is there a trigger on the table?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 6:00 am
Ha, getting there,
Yes there is a trigger, when i create the same table without the trigger then all is ok.
But not with the trigger?
USE [infocarenotts-test]
GO
/****** Object: Trigger [dbo].[trg_UpdateProcessedData] Script Date: 05/20/2008 12:59:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
-- Create date:
-- Description:
-- =============================================
ALTER TRIGGER [dbo].[trg_UpdateProcessedData]
ON [dbo].[ProcessedData]
AFTER INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
UPDATE BIS.History
SET ProcessedData = 1
WHERE ID IN (
SELECT HistoryID
FROM INSERTED
WHERE RetroFlag = 0
)
UPDATE BIS.[999]
SET ProcessedData = 1
WHERE ID IN (
SELECT HistoryID
FROM INSERTED
WHERE RetroFlag = 1
)
DELETE ProcessedData
WHERE HistoryID IN (
SELECT HistoryID
FROM INSERTED
)
END
May 20, 2008 at 6:04 am
Do the tables BIS.History, BIS.[999] and ProcessedData all exist?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 6:37 am
Yes they do,
Think I will have to remove the trigger for now and get the developers to look into this.
Unless you can spot any think else that could be so obvious
But thanks for the help you have given at least I have made headway.
Thanks
May 20, 2008 at 7:27 am
Actually, I just noticed this little gem...
ALTER TRIGGER [dbo].[trg_UpdateProcessedData]
ON [dbo].[ProcessedData]
AFTER INSERT
....
DELETE ProcessedData
WHERE HistoryID IN (SELECT HistoryID FROM INSERTED)
ie, the trigger deletes all the rows that have just been inserted, after updating the BIS.history and the BIS.[999] tables
So, the inserts that don't look like it's by design. 🙂
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 8:10 am
Now that is interesting! Functionally, the table has no purpose except to run that trigger. No data can ever be added to it. What an odd way to get data into the other two tables. I've never seen that idea before.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 11:44 am
Indeed. Is most intriguing...
Personally, I'm thinking that an instead of trigger might be a better idea, as it allows the insert to not actually be run, removing the need for a delete at all.
Oh, note that the trigger is updating the other 2 tables, not inserting into them. So rows inserted into this table go nowhere, they just set the processed flag on matching rows in 2 other tables
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 12:42 pm
GilaMonster (5/20/2008)
Indeed. Is most intriguing...Personally, I'm thinking that an instead of trigger might be a better idea, as it allows the insert to not actually be run, removing the need for a delete at all.
Oh, note that the trigger is updating the other 2 tables, not inserting into them. So rows inserted into this table go nowhere, they just set the processed flag on matching rows in 2 other tables
I didn't think of an instead of trigger (which would reduce the amount of logging, too). My thought was just put this logic in the proc that otherwise inserts into this table, and bypass the whole trigger and insert thing completely. But an instead of trigger would definitely be a step in the right direction.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 20, 2008 at 12:44 pm
GSquared (5/20/2008)
GilaMonster (5/20/2008)
Indeed. Is most intriguing...Personally, I'm thinking that an instead of trigger might be a better idea, as it allows the insert to not actually be run, removing the need for a delete at all.
Oh, note that the trigger is updating the other 2 tables, not inserting into them. So rows inserted into this table go nowhere, they just set the processed flag on matching rows in 2 other tables
I didn't think of an instead of trigger (which would reduce the amount of logging, too). My thought was just put this logic in the proc that otherwise inserts into this table, and bypass the whole trigger and insert thing completely. But an instead of trigger would definitely be a step in the right direction.
It might be, unless the musical genius that came up with this scheme put something creative in the ON DELETE trigger of the same table....:)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 12:57 pm
GSquared (5/20/2008)
My thought was just put this logic in the proc that otherwise inserts into this table, and bypass the whole trigger and insert thing completely. But an instead of trigger would definitely be a step in the right direction.
It's probably a direct insert from the front end, not using a stored proc at all.
Matt Miller
It might be, unless the musical genius that came up with this scheme put something creative in the ON DELETE trigger of the same table....
One hopes not.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 1:02 pm
GilaMonster (5/20/2008)
GSquared (5/20/2008)
My thought was just put this logic in the proc that otherwise inserts into this table, and bypass the whole trigger and insert thing completely. But an instead of trigger would definitely be a step in the right direction.It's probably a direct insert from the front end, not using a stored proc at all.
Matt Miller
It might be, unless the musical genius that came up with this scheme put something creative in the ON DELETE trigger of the same table....One hopes not.
Agreed - but this kind of code would make me look twice, just to be sure there's no "extra cute" happening.:hehe:
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 20, 2008 at 1:18 pm
GilaMonster (5/20/2008)
GSquared (5/20/2008)
My thought was just put this logic in the proc that otherwise inserts into this table, and bypass the whole trigger and insert thing completely. But an instead of trigger would definitely be a step in the right direction.It's probably a direct insert from the front end, not using a stored proc at all.
Even if it's not a proc, I don't think I've run into a language yet that could do SQL inserts, but couldn't do updates.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply