The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Cascading deletes, but with flags

I'm building an inventory database and want to use a form of cascading deletes. So if I delete a department, it will delete all the categories in that department, and then all the stockitems in those categories... and so on.

However, we don't delete rows, we make them inactive (i.e. ActiveYN = 0). So, if you make a department inactive, then all the children under that department should be inactive.

I guess this would be something that I could with triggers, but before I start, I would really like to get advice on the best way to do this, as this does seem like quite a generic thing I'm try to do.

Thanks, Craig
Craig Send private email
Thursday, August 24, 2006
 
 
Forgot to add : I'm using SQL Server 2000, but plan to upgrade to 2005 soon.
Craig Send private email
Thursday, August 24, 2006
 
 
Dont use triggers.

Change your flags in a transaction.

My 2 cents worth.
yesitisme
Thursday, August 24, 2006
 
 
Craig,

Really think very, very hard about cascade deletes (and triggers as well).

I was talking to someone the other day about triggers.

Basically, you can end up in situations that have multiple triggers firing. Often trying to deal with triggers when you are debugging an intermittent problem is a real pain.

If you want to delete everything tied to a department, put it in a stored proc and make sure to do it all in one transaction.

That way you don't delete half the stuff you need to.

If you're not used to using transactions or have any questions you don't want to post, feel free to shoot me an email.
Anon for this one... Send private email
Thursday, August 24, 2006
 
 
Within Oracle this is easy enough to do with triggers. But what about your reverse situation--I'm assuming that it's ok to have inactive items for an active department, but not vice-versa.
George Jansen Send private email
Thursday, August 24, 2006
 
 
Kinda lame for me to point out a potential issue and not write any code huh?

create proc dbo.del_DeptCateStock (
@DepartmentID int
)

as

--variable to track potential errors
declare @error int
set @error = 0

--start our transaction
BEGIN TRAN

--inactivate all the stockitems
update tblStockItem
set ActiveYN = 0
from tblStockItem
inner join tblCategory c
where c.DepartmentID = @DepartmentID

set @error = @@error

if @error = 0
begin
--inactivate all the categories
update tblCategory
set ActiveYN = 0
from tblCategory
where tblCategory.DepartmentID = @DepartmentID
end

set @error = @@error

if @error = 0
begin
update tblDepartment
set ActiveYN = 0
where DepartmentID = @DepartmentID
end

--if no error, commit
IF @error = 0
THEN COMMIT TRAN
--if there's an error, rollback
ELSE ROLLBACK TRAN
END
Anon for this one... Send private email
Thursday, August 24, 2006
 
 
Oops.

Forgot the on statement.

Above, this:

--inactivate all the stockitems
update tblStockItem
set ActiveYN = 0
from tblStockItem
inner join tblCategory c
where c.DepartmentID = @DepartmentID

should be this:

--inactivate all the stockitems
update tblStockItem
set ActiveYN = 0
from tblStockItem
inner join tblCategory c
on tblStockItem.CategoryID = c.CategoryID
where c.DepartmentID = @DepartmentID
D in PHX Send private email
Thursday, August 24, 2006
 
 
Thanks for the advice. From my experience of using triggers, I know why you are weary of over-using them. I'll take your advice: use a stored procedure and wrap the statements in a transaction. Thanks.
Craig Send private email
Thursday, August 24, 2006
 
 
No problemo Craig.

Shoot me mail if ya need a hand.
D in PHX Send private email
Thursday, August 24, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz