Informed Delete consent

[David W. Fenton]

Yes, you have to use tools such as Rick's Find and Replace to find
all locations where the tables are used and add the Where clause.

A much simpler way to do this is to rename the base tables and
create a query with the original names of the base tables that
filters out the deleted records. Then you don't have to change
anything in your existing forms/reports/queries, because you've
replaced the original unfiltered tables with filtered queries having
the same name.

 

[Michel Walsh]

What IDENTITY_INSERT has to do with triggers?


In what triggers change anything about the wrong solution you gave and which
said you edit an autonumber with IDENTITY_INSERT?


Does I know about triggers? Yup. And I share Joe Celko's opinion about them.



Vanderghast, Access MVP


uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?

 

[Michel Walsh]

Is a jackass ANYONE showing you are wrong on a technical point?


If so, I am proud to be one, and I let any reader to conclude by him/her
self what kind of person you are.


Vanderghast, Access MVP


uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?
uh, do you know _ANYTHING_ about triggers, jackass?

 

[aaron.kempf]

because there isn't a scenario in the world-- that I couldn't solve
with triggers.

Oh, you want to update an identity column?
I'll just use triggers to do it on the server side

-Aaron

 

[Michel Walsh]

You can? please supply the code, executable on Northwind table Categories.
Your code, in your trigger, OR NOT, is likely using SQL or TRANSACT-SQL, no?
So, using triggers OR not, is not the MAIN point of the solution.

SUPPLY an example of SQL / TRANSACT-SQL that UPDATE an existing record,
resetting its autonumber, if you can. What you proposed up to now DOES NOT
WORK, as I already supplied the code that you can cut and paste into SQL
Server and make the experimentation by yourself ( and as can do any
reader ).



Triggers are evil, in most cases they are used like this: insert an roof,
and let the triggers install the required walls automatically for you,
which, in turn, always with triggers, let them install the required
foundation.

The 'right' way to do that, instead, is to start a transaction, make the
foundations as they are required for your intended building, then add the
walls the way your requirements would be, next, add the roof. Commit the
transaction if every thing is right.

Why the trigger approach is wrong? because it only supplies 'default' walls
and 'default' foundation' and if you *ever* want to implement the
transaction approach, with some particular out of standard foundations or
walls, then the walls inserted by the triggers will collide with your
intended not-default walls.

Triggers have some valid use, but that is rather exceptional. If you cannot
make a ***single*** database job without using triggers, I suspect your
databases are a mess, either really poorly designed, either poorly
upgradeable (able to accommodate new walls, to continue the house analogy).


BUT AGAIN, triggers is not THE technical point to UPDATE an autonumber,
since even if you do it with triggers, you are to use SQL. So, ... the
***SQL*** code is...



Vanderghast, Access MVP


because there isn't a scenario in the world-- that I couldn't solve
with triggers.

Oh, you want to update an identity column?
I'll just use triggers to do it on the server side

-Aaron

 

[Tony Toews [MVP]]

A much simpler way to do this is to rename the base tables and
create a query with the original names of the base tables that
filters out the deleted records. Then you don't have to change
anything in your existing forms/reports/queries, because you've
replaced the original unfiltered tables with filtered queries having
the same name.

That would work but I consider that to be a kludge.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Tony Toews [MVP]]

I agree with you on CASCADE UPDATE -- it only makes sense with
natural keys, and there are very few of those in any of my
applications.

On CASCADE DELETE, I use it sparingly. By default, I have it OFF,
but in certain cases, such as the detail records for an invoice, it
makes sense to have it ON, as there is no reason *not* to delete
those details if the invoice header is being deleted.

We've had this discussion before I think. <smile>

For the extra minute or two it takes I use SQL in VBA to delete the
relevant detail records.

And your complaint about the default error message is *also*
misplace, in my opinion, as any deletion you allow a user to do
should be under the control of your own code. It's only when you
rely on Access default behaviors that you're going to get the
default Access confirmation message, and the fault there is not in
having CASCADE DELETE turned on, but in not coding your own
user-friendly confirmation message.

But note that I used the weasel words in my posting: "if someone ever
gets to the table view then they can do a lot of damage without
realizing it."

So, in short, I think your blanket condemnation of CASCADE DELETE is
unfounded. Nearly every app of mine has one or two relationships
with it turned on (because the child records have no reason for
existing independent of their parent). But I have learned over the
years that it should be OFF in most relationships, simply because
not doing so will lose too much data that's crucial to be retained
(you really don't want to delete a customer with invoice records,
for instance, because then your books are completely hosed).

This then is a personal preference. For the extra two minutes it
takes to code the SQL Delete in VBA I feel it's worth it.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

That would work but I consider that to be a kludge.

While it does incur a "technical debt":

http://www.codinghorror.com/blog/archives/001230.html

it can be quite time-saving if you need to do this kind of thing
very quickly.

I've got a SQL Server app that still depends on a bunch of views to
alias the real field names to the awful ones that were used in the
front-end app that predates my involvement in the project by about 6
years. I could very easily spend the time searching and replacing
all the occurences of the awful names and then get rid of the views
that do the aliasing, but, well, why bother? It works. I know what's
going on, and as long as I'm involved I think I'm not going to
forget this fact. Yes, of course, should I get hit by a bus,
somebody would probably puzzle over the fact that the fields in the
underlying tables don't match those used in the app -- but the
pattern would become clear really quickly, and heaven knows, it's
probably one of the least problematic aspects of this app that races
its lineage back to pre-Access 2 days (and was developed by someone
who didn't know a damned thing about anything at all).

 

[David W. Fenton]

[To Aaron:]

Is a jackass ANYONE showing you are wrong on a technical point?

By default, one should assume that any post by Aaron is wrong on any
technical point it might be making. The exception would be the
occasional post by Aaron without any factual errors.

(and this includes his posts about SQL Server, which seem to have
almost as many falsehoods in them as his posts about Access and Jet)

 

[David W. Fenton]

Triggers have some valid use, but that is rather exceptional.

I find triggers most useful in circumstances where I have to keep
denormalized data up-to-date. This requirement is often due to
performance issues, or outside dependencies on other data sources.

 

[aaron.kempf]

I CAN DO ANYTHING I WANT IN TRIGGERS

and yes-- the comparsion 'TSQL + VBA + .NET + REPORTING SERVICES' vs
'JET + VBA' is a valid comparison.

I can literally and honestly.. build a trigger that will download a
webpage.. or whatever the **** I want to do.
Maybe I will give a code sample-- Maybe I won't.

I don't need to justify jack shit-- your database fucking sucks a big
fat dick and anyone that uses Jet for anything is _JUST_PLAIN_STUPID_.

-Aaron

 

[aaron.kempf]

David;

you're a cocksucking liar.. You've never used SQL in your whole life.

Anyone using SQL would use ADP-- because it's a more efficient
platform.

Sorry-- but eat shit Jet crybaby _BITCH_

-Aaron

 

[aaron.kempf]

David;

you're full of shit _FAGGOT_ don't you know that you're a little Jet
crybaby-- and you can't even spell SQL?

-Aaron

 

[aaron.kempf]

this is probably a little more verbose than it needs to be.. and
it's aircode.. but it's a simple stored procedure that allows me to
update an identity value.
I'm pretty sure I could automate it, make it a hell of a lot more
compact
Especially using OUTPUT clauses in SQL 2005

create procedure spUpdateEmployeeID
(
@OldEmpID INT,
@NewEmpID INT
)
AS
Select *
Into #Employees
Where EmployeeID = @OldEmpID

Delete From
Employees
Where EmployeeID = @OldEmpID

Set Identity_Insert Employees On

Update #Employees Set EmployeeID = @NewEmpID

Insert Into Employees
Select *
From #Employees

 

[Michel Walsh]

Indeed, that is one of their use where I found them useful.


Vanderghast, Access MVP

 

[Michel Walsh]

That is an apparently working solution, at first glance, but it has a BIG
problem.

The DELETE statement may delete MORE than the single tagged record, in case
there are cascade delete OR triggers doing the same effect, many others
records will ALSO be deleted, in other tables!

So, you don't technically UPDATE one record, but *potentially* DELETE many
records then INSERT one. It is far from universal solution, because of its
many side effects, which are not 'implicitly' wanted when someone speak of
an UPDATE.


Vanderghast, Access MVP



this is probably a little more verbose than it needs to be.. and
it's aircode.. but it's a simple stored procedure that allows me to
update an identity value.
I'm pretty sure I could automate it, make it a hell of a lot more
compact
Especially using OUTPUT clauses in SQL 2005

create procedure spUpdateEmployeeID
(
@OldEmpID INT,
@NewEmpID INT
)
AS
Select *
Into #Employees
Where EmployeeID = @OldEmpID

Delete From
Employees
Where EmployeeID = @OldEmpID

Set Identity_Insert Employees On

Update #Employees Set EmployeeID = @NewEmpID

Insert Into Employees
Select *
From #Employees

 

[Tony Toews [MVP]]

While it does incur a "technical debt":
http://www.codinghorror.com/blog/archives/001230.html

Nice posting on the topic. Hmm, I have no idea why my blog reader
isn't seeing that blogs postings since Feb 21st.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[David W. Fenton]

Nice posting on the topic. Hmm, I have no idea why my blog
reader isn't seeing that blogs postings since Feb 21st.

Well, it's funny we should have just discussed this, as a user
reported a bug just yetserday in an infrequently used area of the
app in question that was related to the fact that a form was using
the view with the alias in one context, and the base table without
the alias in another. So, I just set up the view to have both the
unaliased and aliased field.

Kludge?

Yes!

But it got the bug fixed without having to audit the whole app to
see where the thing was in use and then fix it overall.

Does the front end eventually need to be updated to not use the old
field name, and to use the actual field names in the SQL Server
tables? Yes, of course. But I don't think the people paying the
bills are going to get to hot for spending money on something that
won't do anything to improve the way the app behaves for *them* (and
if I make any mistakes, will produce new bugs that don't currently
exist).

 

[Larry Kahm]

The client approved the following solution: Display the records that are
about to be deleted and then archive them into a csv file (on the off-chance
the data might be needed), but to physically delete the records from the
database.

As per the recommendations, I'll review all the relationship links to ensure
that the cascade bits are removed.

When developing new applications I will always include an "inactive" flag
for record delete and build the appropriate queries. In this case, the
client is not willing to go through the expense of a retrofit. And, quite
frankly, I really don't want to rename several dozen tables and have queries
with a "tbl" prefix (because, despite the documentation, someone else
maintaining this app in the future is bound to screw up).

Thanks for this discussion, I really appreciate the insights!

Larry

 

[Armen Stein]

When developing new applications I will always include an "inactive" flag
for record delete and build the appropriate queries.

That's a good practice. But see below about building those queries.

In this case, the
client is not willing to go through the expense of a retrofit.
Understandable.

And, quite
frankly, I really don't want to rename several dozen tables and have queries
with a "tbl" prefix (because, despite the documentation, someone else
maintaining this app in the future is bound to screw up).

That's good, because this approach has another downside that I didn't
see mentioned. Any comboboxes that are based on a surrogate key
recordset where deactivated records have been omitted will simply
appear as blank, even though have are loaded with a key underneath.
This will be very disconcerting to the user.

Comboboxes need to be able to show inactive records. A classic case
is the Salesperson for an Order. "Biff" doesn't work here anymore,
but he was the Salesperson for some historical Orders. In our shop,
we use this technique:

For comboboxes that could include inactive records, we use a recordset
to sort the inactive ones to the bottom, and concatenate an asterisk
before them. So the combobox test shows:

*Biff

And the dropdown list shows:

Armen
Betty
Charlie
*Aaron
*Biff
*Cathy

This serves several purposes:

- If someone starts typing into the combobox, they aren't going to
select an inactive record, because they didn't type the *.
- Inactive records still appear in the combobox for historical
records, but the * indicates that they are inactive.
- If a user really does select an inactive record, a warning or error
message can be displayed in the BeforeUpdate event of the combobox.

We've used this technique in many projects and it's part of our
standard practices now. Users seem to understand it readily, and it's
pretty easy for our developers to code.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com