• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 313
  • Last Modified:

When I right click on row in Enterprise Manager and "delete" the row, next time I run the query the row is still there.

I have tried to right click on the row itself and delete it(it goes away until I run the query again), or run a "delete from" query(says row has been affected). I get no errors, the row just doesn't really go away. I did not create this database and am not sure what setting would cause this behavior. I am a .NET programmer that works with SQL 75% of my day, however, I do not know all the ins and outs by any means. Any help on this would be great! I need to get rid of some really old data.

Thank you in advance,

tley
0
taylor11
Asked:
taylor11
  • 6
  • 5
  • 2
  • +1
1 Solution
 
johnclarke123Commented:
Can you post the script for the table - there may be triggers, for example, that prevent deletions.
Go to Query Analyzer, select the table in the Object Browser, right-click and choose Script Object to Clipboard as Create.
0
 
ptjcbCommented:
Are you doing a refresh of the objects after you run the delete query?
0
 
taylor11Author Commented:
CREATE TABLE [ddPermissions] (
      [UserID] [int] NOT NULL ,
      [ObjectID] [int] NOT NULL ,
      [Permissions] [int] NOT NULL CONSTRAINT [DF_ddPermissions_Permissions] DEFAULT (0),
      [CreatedBy] [nvarchar] (20) COLLATE Latin1_General_BIN NULL ,
      [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ddPermissions_CreateDate] DEFAULT (getdate()),
      [UpdatedBy] [nvarchar] (20) COLLATE Latin1_General_BIN NULL ,
      [LastUpdate] [datetime] NULL ,
      [Active] [bit] NULL CONSTRAINT [DF_ddPermissions_Active] DEFAULT (1),
      [PermID] [int] IDENTITY (1, 1) NOT NULL ,
      CONSTRAINT [PK_ddPermissions] PRIMARY KEY  CLUSTERED
      (
            [PermID]
      ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY]
GO
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
HDatabase AdministratorCommented:
Would it be possible for you to post the querry you are useing to delete the row of data and the querry you are useing to view the row of data?
0
 
taylor11Author Commented:
SELECT     *
FROM         ddPermissions
WHERE     (UserID = '2001000066')

which gives me:

2001000066      82      15      bobf      3/25/2002      bobf      3/25/2002      0      123
2001000066      81      1      bobf      3/25/2002      bobf      3/25/2002      0      124

DELETE FROM ddPermissions
WHERE     (UserID = '2001000066')

I have the same problem with this table:

CREATE TABLE [ddUsers] (
      [UserID] [int] IDENTITY (2000001, 1) NOT NULL ,
      [UserCallName] [nvarchar] (50) COLLATE Latin1_General_BIN NOT NULL ,
      [NTUserID] [nvarchar] (20) COLLATE Latin1_General_BIN NOT NULL ,
      [CreatedBy] [nvarchar] (20) COLLATE Latin1_General_BIN NULL ,
      [CreateDate] [datetime] NOT NULL CONSTRAINT [DF_ddUsers_CreateDate] DEFAULT (getdate()),
      [UpdatedBy] [nvarchar] (20) COLLATE Latin1_General_BIN NULL ,
      [LastUpdate] [datetime] NULL CONSTRAINT [DF_ddUsers_LastUpdate] DEFAULT (getdate()),
      [Active] [bit] NOT NULL CONSTRAINT [DF_ddUsers_Active] DEFAULT (1),
      CONSTRAINT [PK_ddUsers] PRIMARY KEY  NONCLUSTERED
      (
            [UserID]
      ) WITH  FILLFACTOR = 90  ON [PRIMARY]
) ON [PRIMARY]
GO

I am deleting from ddPermissions first then deleting from ddUsers. I didn't think it would matter, but there is an access database that has tables linked to these.
0
 
HDatabase AdministratorCommented:
Do you have some form of replication or imports/syncing with the access db or another data source that might be re-adding this particular record back?
0
 
taylor11Author Commented:
When I change one of the columns in the linked access table then go to sql and do the simple select, it is changed in SQL. I don't know how this is happening. I don't care for access that much. I tried to delete the record in access and then also delete it in SQL, but  both still showed the record after deleting in both places.
0
 
HDatabase AdministratorCommented:
mmm does sound like you have some type of trigger ...like johnclarke123  said above...or some type of automatic update...

First thing is check for triggers in the table

Enterprise Manager:

- go to Tables
- right click on the table select Design Table.  
- On the toolbar on the top of the screen click the button for the triggers beside the key.  
- this will open the trigger properties for the table were you can view the triggers on this table...
0
 
taylor11Author Commented:
It just shows:

CREATE TRIGGER [TRIGGER NAME] ON [dbo].[ddPermissions]
FOR INSERT, UPDATE, DELETE
AS

I'm guessing this is just a template and there are no triggers?
0
 
taylor11Author Commented:
Sorry, I submitted that a little too quick.

CREATE TRIGGER CancelPermDelete ON ddPermissions
FOR DELETE
AS
  DECLARE @Identity int  
  SET IDENTITY_INSERT ddPermissions ON
  INSERT INTO ddPermissions(PermID,UserID, ObjectID,Permissions,CreatedBy,CreateDate,UpdatedBy,LastUpdate)
  SELECT PermID,UserID, ObjectID,Permissions,CreatedBy,CreateDate,UpdatedBy,LastUpdate FROM deleted
  SELECT @Identity = @@IDENTITY
  UPDATE ddPermissions Set Active = 0 WHERE PermID = @Identity
  SET IDENTITY_INSERT ddPermissions OFF

this is the only trigger in the drop down
0
 
HDatabase AdministratorCommented:
looks like you have a table called deleted thats adding the record back..

Do you know who designed this table and what it is used for?
0
 
HDatabase AdministratorCommented:
My fist guess is they didnt intend for records to be delete but to set a flag to '0' in a field called active..
0
 
taylor11Author Commented:
Thank you hollecar,

The developers that created the whole mess did not do a very good job in many areas. I'm guessing they didn't want the dependent tables to have rows deleted and mess up foreign key-like columns. I saved copies of the triggers on the two tables and then deleted them from the tables. I am now able to delete rows where needed. I think it was just something extra and unnecessary the past developers did just to see if they could.

Thank you again,

tley
0
 
johnclarke123Commented:
(Sorry I skipped out on this one - my daughter fell down the stairs and I had to take her to hospital; she's fine now)

I think you're right, taylor11.  'Deleted' isn't a real table; it's a way of examining the contents of a deleted row within a trigger, as you've found out.  The developers clearly decided to set a flag to 0 rather than allow a row to be actually deleted.  

Actually, that's a pretty good design principle in most cases.  Your approach, to when you _really_ do need to delete rows will work, but make sure you understand where this may have unintended consequences.  Smart developers would have left you some documentation, but you may not have been lucky enough to have had that kind.

jc
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 5
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now