What is INACTIVE delete? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

What is INACTIVE delete?

Author

Topic

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-10 : 06:25:45

Here are two tables. How do i create an SP for INACTIVE delete and how do i use the UNIQUE constraint to avoid duplication of the NAME columns(logingroup_name and login_name) in these tables?

--Creating Table ref_LoginGroup:-
create table ref_LoginGroup
(
logingroupid tinyint identity(1,1) constraint pklogingroupid primary key clustered,
logingroup_name varchar(25) constraint chkname check(logingroup_name NOT LIKE '% %'),
changed_login_id int,
is_active bit,
lastupdr smalldatetime,
)
--Creating Unique nonclustered Index for ref_LoginGroup:-
create unique nonclustered index idxlgn on ref_LoginGroup(logingroup_name)

--Creating Table ref_Login:-
create table ref_Login
(
login_id int identity(1,1) constraint pkloginid primary key clustered,
login_name varchar(50) constraint chklogname check(login_name NOT LIKE '% %'),
firstname varchar(30),
lastname varchar(30),
logingroupid tinyint constraint fklogingroupid foreign key references ref_LoginGroup(logingroupid),
changed_login_id int,
is_active bit,
lastupdr smalldatetime
)
--Creating Unique nonclustered Index for ref_Login:-
create unique nonclustered index idxl on ref_Login(login_name)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 06:31:54

1. You should do this type of Checking in the Front End Application and Send only VALID data to SQL Server table

2 How do you define Inactive Records?

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-10 : 06:50:46

1)Doesnt it sound like THE BEGENING OF THE END? My team leader has asked me to do this in SQL Server using constraints.
2)Inactive records-im not really sure, but he told to create a column 'is_active' with BIT datatype wich accepts only '0' or '1'. when the user tries to delete the record, it should mark it as inactive and not really delete it physically.

Imran
"Do not try and bend the spoon physically. Insted only try to realize the truith-There is no spoon. Then u would realize that it is not the spoon which bends, but it is you!"-THE MATRIX.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 06:59:33

1 Alter Table TableX Alter column loginName varchar(30) Check (loginName not like '% %')
2 Update yourTable set is_active=0 where <Condition> --To set it Inactive

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-10 : 08:12:25

Thanx for the 1st one. The constraint is working.
2)I dont understand why we're just creating a column 'is_active' and marking it either '0' or '1'? I mean even if i set the value to 0 the record is still displayed when i give a select statement. Maybe i do not understand what inactive,active delete is.Can u please explain what my TL meant by inactive and active delete?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-10 : 08:39:21

In your Select Statement you need to use Where Condition

Select Columns from yourTable where is_active=1

Madhivanan

Failing to plan is Planning to fail

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-10-10 : 08:43:45

"Can u please explain what my TL meant by inactive and active delete?"
....

maybe the question should be asked of your TL!!!!!....we are only be guessing!

X002548
Not Just a Number

15586 Posts

Posted - 2005-10-10 : 08:48:37

I like it when I talk to my techs, and they say yes, and have no clue.

I always look for the blank stare and ask them if they understand what I meant. When they say yes (which, they always do), I ask them to explain it to me.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

HCLollo
Starting Member

49 Posts

Posted - 2005-10-11 : 05:43:03

IK,
look at active/inactive delete as using the Recycle Bin:
0 - the data is valid
1 - the data is probably to be deleted

Afterwards, you can periodically delete all inactivated data.

If the Recycle Bin metaphor doesn't work, look at it like the
partially deleted mail in a webmail service, like Yahoo or Hotmail.

HCL

"If it works fine, then it doesn't have enough features"

Kristen
Test

22859 Posts

Posted - 2005-10-11 : 05:48:40

You might want to access the table via a VIEW so that client stuff doesn't get access to records flagged "inactive" simply because they forgot to put it in the WHERE clause!!


CREATE TABLE MyCustomers_RAW
(
   MyID int,
   MyName varchar(50),
   ...
   IsActive BIT
)

CREATE VIEW MyCustomers
AS
SELECT
   MyID,
   MyName,
   ...
-- IsActive
FROM MyCustomers_RAW
WHERE IsActive = 1

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-12 : 00:32:59

Thats good Approach Kris

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 02:04:30

iktheone,
It seems that you edited the topic
What did you add additionally in your question?

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-14 : 02:17:03

well, the first point i asked was "how do i restrict names to not have BLANK SPACES". Well u already answered to that question. which leaves us with only one more question mr.Madhivanan...what is INACTIVE delete?

Imran
"Out here you have to let it all go NEO, doubt..fear...disbelief."-Morpheus(MATRIX)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 02:25:03

>>what is INACTIVE delete?

Changing the Status column to 0 without deleting the records
In your select statement you need to use where Status=1 so that you can retreive only active records. AFAIK, this is what Inactive Delete

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-14 : 02:33:42

ok thats cool. i understood. but how do i use UNIQUE INDEX to restrict users from entering duplicate names? u can find the two tables at the top of this page. when i create this unique index and try to enter duplicate values, it gives me an error message. can it be made more USER FRIENDLY?

Imran,
--What is the meaning of "AFAIK"? well u can either call me IK or IK THE ONE(thats how u pronounce it! :-) )

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 02:39:11

If you use Front End Application, then you can check it and give the custom message you want

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-14 : 02:49:33

well, im not using any front end application. this is purely SQL Server. and it is also just for demo, so what do i do? first of all we've created a Unique Index, but how and where do we use it?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 02:55:43

Create Stored Procedure to insert data and check for the existing of the name. If exists dont insert else insert the records

Create Procedure yourSP
( -- parameters )
as
If exists(Select * from yourTable where name=@name)
--Name already exists
else
--Insert it to table

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-14 : 04:53:57

Thank you mr.Madhivanan. Its working.
By the way im from Chennai. Where are you from?
Imran,

"In life, there is no answer to-How? When? What? Where?...etc"--Imran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 05:21:17

>>By the way im from Chennai. Where are you from?

This will tell you

http://sqlteam.com/forums/pop_profile.asp?mode=display&id=14266

Madhivanan

Failing to plan is Planning to fail

iktheone
Yak Posting Veteran

66 Posts

Posted - 2005-10-18 : 11:02:00

Houston we have a problem!

mr.madhivanan, this is my sp for fetching ALL,Inactive and Active records:

create procedure F_ref_Login
@is_active bit
as
BEGIN
select
login_id,login_name,
firstname,lastname,
changed_login_id,
lastupdr
from ref_Login
where is_active=@is_active
END

as you can see im passing a single parameter @is_active which will either accept 0 or 1 that is bit value. but with this i can get only Inactive and Active records. How will i get both Inactive and Active records(ALL) in the same SP?
Imran,
Incomiiiiiiiiiiiinnnnnnnnggggggg!!!....

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources