Deleting semi duplicates

Suppose that I have a table that contains a lot of records that are
identical except for an id field and a date-time-stamp field. For
example

Id Unit Price DTS
1 A 1.00 Date 1
2 A 1.00 Date 2
3 A 1.00 Date 3
4 B 1.25 Date 4
5 B 1.50 Date 5
6 B 1.50 Date 6
7 C 2.75 Date 7
8 C 2.75 Date 8
9 C 2.75 Date 9
10 C 3.00 Date 10

I want to cull out records that are duplicates in the units and price
fields. I want to use the max DTS as the criteria for which record in
a set of "duplicates" will remain. So, If I get the right query, I
should return with

Id Unit Price DTS
1 A 1.00 Date 1
4 B 1.25 Date 4
5 B 1.50 Date 5
7 C 2.75 Date 7
10 C 3.00 Date 10

Is this possible using a single query? If so, how? I am sure that I
can do this using code, but it will involve a bunch of loops and
process time. I would prefer a cleaner, more elegant way. Thanks for
any help.

JerryAssuming the combination of (unit,price,dts) is unique and non-NULL:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE unit = Sometable.unit
AND price = Sometable.price
AND dts > Sometable.dts)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<zemdnRnYsNSQopHdRVn-vw@.giganews.com>...
> Assuming the combination of (unit,price,dts) is unique and non-NULL:
> DELETE FROM Sometable
> WHERE EXISTS
> (SELECT *
> FROM Sometable AS S
> WHERE unit = Sometable.unit
> AND price = Sometable.price
> AND dts > Sometable.dts)

Thanks. I'll give this a try.

J|||Remember that names are resoved to the nearer containing table
reference! You meant:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE S.unit = Sometable.unit
AND S.price = Sometable.price
AND S.dts > Sometable.dts);|||> Remember that names are resoved to the nearer containing table
> reference!

Precisely. That's why the S isn't needed here - the alias ensures that
"Sometable" refers to the outer reference and the other columns to the inner
reference. Your statement is equivalent to mine.

--
David Portas
----
Please reply only to the newsgroup
--sql

deleting rows based on nvarchar data

I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?delete tableA where fieldB = ?

Is that what you mean?|||Kinda. I only have one table and want to delete specific rows from that table that have a specific data within a certain field.

Let be more specific. I have a table (tableA) with 10 fields. Field 3 has data that does not conform to a datetime format and I would like to remove it. The field is currently a nvarchar(50) type (2003-10-10).

I want to remove rows that contain data that is looks like this
(0020-10-10). Make sense?|||delete from table where field3 ='0020-10-10'|||Perhaps you can use the ISDATE() function, which returns 1 if a string can be converted to a valid date, and 0 if it cannot.

Try this query:

select *
from YourTable
where ISDATE([Column3]) = 0

If this returns the rows you want deleted, then change the query to a delete query:

delete
from YourTable
where ISDATE([Column3]) = 0|||right but I forgot to mention, there are all kinds of variation of that date.

I ran a script that reads as follows to help identify data within a field that does not fit a date format->

SELECT * FROM findet WHERE ISDATE(servfrom) = 0

This gave me a list of records that are not in proper date format. Now, I would like to remove them from my table. Can I use the same,

delete findet where ISDATE(servfrom)=0|||See previous post.|||That worked like a charm, thank you!!

Deleting leading 0's in numbers stored in a text field.

I am trying to use several tables that have one 10-character text field in
common. Most of the records have a numeric expression, but some tables have leading
0's, and some don't.
I can't cast the field to numbers because there are some records that have
letters also.
What function can I use to get rid of all the 0s at the left of each record?
(Sort of a LTRIM function that gets rid of 0s instead of spaces).

Thanks!

While I am not aware of any built-in function to perform this (maybe a good SQLCLR function candidate :) ), this TSQL will work as tested below...

declare @.Field varchar(10)

declare @.i int

set @.Field = '000123405'

set @.i = 1

--remove leading 0s?

if charindex('0', @.Field) = 1

begin

while @.i <= Len(@.Field)

begin

--character a 0?

if charindex('0',@.Field,@.i) = @.i

begin

set @.Field = substring(@.Field, (@.i + 1), (len(@.Field)-@.i))

end

else

begin

break

end

--increment counter

set @.i = @.i + 1

end

end

select @.Field

|||

Leading zeroes are fine for casting character values to numeric/integer/money data types. So it should be fine without doing any trimming. For the rows that have letters you can filter those using a case expression like:

case when col not like '%[^0-9]%' then cast(col as int) end

or below although this checks for conversions to integer/numeric/money data types

case when isnumeric(col) = 1 then cast(col as int) end

And if you want to strp the leading zeroes you can use the expression below:

substring(col, patindex('%[123456789]%', col), 8000 /* 4000 if col is Unicode */)

|||the code above has a bug.. here is the correct and much efficient code

DECLARE @.i INT
,@.output VARCHAR(MAX)
,@.Input varchar(max)

set @.Input = '0012321'

SET @.i = 1

IF CHARINDEX('0', @.Input) = 1
BEGIN
WHILE @.i <= LEN(@.Input)
BEGIN

IF CHARINDEX('0',@.Input,@.i) = 0
BEGIN
SET @.output = SUBSTRING(@.Input,@.i,LEN(@.Input))
BREAK
END

SET @.i = @.i + 1

END

END

RETURN @.output

deleting hyphens in a phone field

I am trying to delete hyphens and spaces from a field containing phone
numbers. I know that the "Replace" Function should allow me to do this but
I
don't know how. Every phone number is different, so I don't have a 1st valu
e
to put into the arguments. I am very new to SQL Server and this is driving
me nuts.I don't understand what you mean by '1st value'. You can nest REPLACE
functions to remove both spaces and hyphens in one pass:
UPDATE dbo.MyTable
SET Phone = REPLACE(REPLACE(Phone, '-',''), ' ', '')
Hope this helps.
Dan Guzman
SQL Server MVP
"John H." <John H.@.discussions.microsoft.com> wrote in message
news:03E99E25-D30F-4C4E-839D-F25015B38FA2@.microsoft.com...
>I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this
> but I
> don't know how. Every phone number is different, so I don't have a 1st
> value
> to put into the arguments. I am very new to SQL Server and this is
> driving
> me nuts.|||John H. a écrit :
> I am trying to delete hyphens and spaces from a field containing phone
> numbers. I know that the "Replace" Function should allow me to do this bu
t I
> don't know how. Every phone number is different, so I don't have a 1st va
lue
> to put into the arguments. I am very new to SQL Server and this is drivin
g
> me nuts.
This function :
/ ****************************************
***********************************
*/
-- delete all undesirable chars
/ ****************************************
***********************************
*/
-- exemple : FN_RESTRICT('_ Paris...?', 'abcdefghijklmnopqrstuvwxyz')
=>'aris'
CREATE FUNCTION F_RESTRICT (@.IN VARCHAR (8000),
@.CHARSOK VARCHAR(256))
RETURNS VARCHAR (8000)
AS
BEGIN
-- effets de bord
IF @.IN IS NULL
RETURN NULL
IF @.CHARSOK IS NULL
RETURN NULL
IF LEN(@.IN) = 0
RETURN @.IN
-- initialisation
DECLARE @.I INTEGER
DECLARE @.OUT VARCHAR(8000)
SET @.OUT = ''
-- lecture caractère par caractère
SET @.I =1
WHILE @.I <= LEN(@.IN)
BEGIN
IF PATINDEX('%' + SUBSTRING(@.IN, @.I, 1)+ '%', @.CHARSOK) > 0
SET @.OUT = @.OUT + SUBSTRING(@.IN, @.I, 1)
SET @.I = @.I + 1
END
RETURN @.OUT
END
GO
Use it in a trigger :
UPDATE MyTable
SET PHONE_NUMBER = F_RESTRICT (PHONE_NUMBER, '0123456789')
You will have a column with only figures in it.
A +
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************sql

deleting from one table based on rows in another

I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

The tables share the fields CompanyID, CustomerID, OrderNO.If OrderLines table was created with a foreign key constraint on OrderHeader with 'ON DELETE CASCADE' option, alll you have to do is:

Delete From OrderHeader
Where CreateDate <= ExpireDate;

Else, you need first to remove the OrderLines:

Delete From OrderLines L
Where Exists (
Select 1 From OrderHeader H
Where H.CompanyID = L.CompanyID
And H.CustomerID = L.CustomerID
And H.OrderNO = L.OrderNO
And L.CreateDate <= ExpireDate);

And then execute the delete of OrderHeader.|||You can have a daily process (procedure/function) scheduled to run at night to check for any such orders and then delete from OrderLines first and then from OrderHeaders.

Or you could use DBMS_JOB to schedule the procedure/function to do this. Once you have written the correct procedure/function to do this, it is easy to schedule using DBMS_JOB.

Say if you have a procedure 'test_job', you can schedule it as below :

declare
l_job number;
begin
dbms_job.submit(l_job, 'test_job;',sysdate+1/200);
end;
/
commit
/

Hope this helps !!

Originally posted by caf78
I have 2 tables: OrderHeaders and OrderLines which has a one-to-many relationship.
Depending on if the field CreateDate in OrderHeaders has expired som date, I want to delete the corresponding rows in OrderLines AND OrderHeaders... but how?

The tables share the fields CompanyID, CustomerID, OrderNO.

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
Ryan
Create an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>
|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>
sql

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>> Hi All,
>> If I delete a field in SQL Server, will all constraints, foreign keys,
>> indices etc. associated with that field also be removed?
>> Thanks in advance
>> Ryan
>>
>

Deleting Fields

Hi All,
If I delete a field in SQL Server, will all constraints, foreign keys,
indices etc. associated with that field also be removed?
Thanks in advance
RyanCreate an example to try it:
create table #foo (col1 int, col2 int)
create index infoo on #foo (col1)
go
sp_help #foo
go
alter table #foo drop column col1
go
sp_help #foo
The example results in the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'infoo' is dependent on column 'col1'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE DROP COLUMN col1 failed because one or more objects access this
column.
Keith
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> If I delete a field in SQL Server, will all constraints, foreign keys,
> indices etc. associated with that field also be removed?
> Thanks in advance
> Ryan
>|||You're right, I should have tried myself, I thought someone would either
know it or not!
I did test it with Views, and found that if a field is used in a View you
can still delete it. I guess I'll have to do some sort of search on the
system tables to find out if it is used.
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eyAyyfPvEHA.3916@.TK2MSFTNGP10.phx.gbl...
> Create an example to try it:
> create table #foo (col1 int, col2 int)
> create index infoo on #foo (col1)
> go
> sp_help #foo
> go
> alter table #foo drop column col1
> go
> sp_help #foo
>
> The example results in the following error:
> Server: Msg 5074, Level 16, State 8, Line 1
> The index 'infoo' is dependent on column 'col1'.
> Server: Msg 4922, Level 16, State 1, Line 1
> ALTER TABLE DROP COLUMN col1 failed because one or more objects access
> this
> column.
>
> --
> Keith
>
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:Ow7NXQPvEHA.1400@.TK2MSFTNGP11.phx.gbl...
>

Deleting Data From DB

I have edited the aspnet_Users_CreateUser stored procedure so that the UserId that is created when a new user is created is copied to a UserId field in another table. However, when I use the website administration tool to delete users that have been created, it gives me an error saying the delete statement conflicted with the reference constraint. I then added the following code in the aspnet_Users_DeleteUser procedure...

IF((@.TablesToDeleteFrom & 16) <> 0AND

(

EXISTS(SELECT name FROMsysobjectsWHERE(name= N'vw_tt')AND(type ='V'))))BEGINDELETE FROMdbo.userclasssetWHERE@.UserId = UserIdSELECT@.ErrorCode = @.@.ERROR,

@.RowCount = @.@.ROWCOUNT

IF( @.ErrorCode <> 0 )GOTOCleanupIF(@.RowCount <> 0)SELECT@.NumTablesDeletedFrom = @.NumTablesDeletedFrom + 1 hdhd

END

This code was then added to the function at the end which deletes the data from the aspnet_Users table when everything else has been removed

(@.TablesToDeleteFrom & 16) <> 0

AND

Now when I delete a user in the website admin tool, it "deletes" (with no error) the user from the list but doesnt actually physically delete it from the database.

Any ideas?

Problem fixed. The following code seemed to do the trick. I just needed to place it in the right point in the code which was just before the deletion of the UserId information in the membership table. Sorry to waste peoples time!

DELETE FROM dbo.userclassset WHERE @.UserId = UserId

Deleting a field in a table

hi,
I am trying to delete a column in a table. I am not sure there is anything
(ie.SP. Views or triggers) is ref the that field.
There is a way to find out?
Thnaks`You can use sp_depends to list the dependencies. EXEC sp_depends @.objname =
N'table_name'
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/pr...oads/books.mspx
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
> hi,
> I am trying to delete a column in a table. I am not sure there is anything
> (ie.SP. Views or triggers) is ref the that field.
> There is a way to find out?
> Thnaks`
>|||Thanks, Gail
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OYG0JLliGHA.3496@.TK2MSFTNGP04.phx.gbl...
> You can use sp_depends to list the dependencies. EXEC sp_depends @.objname
> = N'table_name'
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/pr...oads/books.mspx
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
>

Deleting a field in a table

hi,
I am trying to delete a column in a table. I am not sure there is anything
(ie.SP. Views or triggers) is ref the that field.
There is a way to find out?
Thnaks`You can use sp_depends to list the dependencies. EXEC sp_depends @.objname =N'table_name'
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"mecn" <mecn2002@.yahoo.com> wrote in message
news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
> hi,
> I am trying to delete a column in a table. I am not sure there is anything
> (ie.SP. Views or triggers) is ref the that field.
> There is a way to find out?
> Thnaks`
>|||Thanks, Gail
"Gail Erickson [MS]" <gaile@.online.microsoft.com> wrote in message
news:OYG0JLliGHA.3496@.TK2MSFTNGP04.phx.gbl...
> You can use sp_depends to list the dependencies. EXEC sp_depends @.objname
> = N'table_name'
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> Download the latest version of Books Online from
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:OoAFzpjiGHA.2456@.TK2MSFTNGP04.phx.gbl...
>> hi,
>> I am trying to delete a column in a table. I am not sure there is
>> anything (ie.SP. Views or triggers) is ref the that field.
>> There is a way to find out?
>> Thnaks`
>

Deletes occurring at publisher

I'm using Merge replication on a table with an autoidentity field. All
users are entering data at the publisher (no records are ever deleted),
and at the subscribers the data is read-only. I'm having a problem
with records being deleted at the publisher. I'm assuming it has to do
with a subscriber having problems, but I can't track it down.
Moving forward (SQL 2005), I want to change the replication type to
transactional. Could someone please tell me the best type of
replication to use in order to prevent records from being deleted at
the publisher?
Any help would be greatly appreciated.
Thanks,
Amy Marshall
Does anything show up in the conflict viewer? The deletes could be logged
here. Are you using join filters? These can cause these types of deletes,
but normally not on the publisher.
Transactional replication is designed for one way replication. It is not
clear to me what your data flow requirements are. With transactional
replication users will be able to delete data in any location, but only
deletes occurring on the publisher will be replicated to the subscriber.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<marshallae@.bowater.com> wrote in message
news:1138635297.513324.39100@.g47g2000cwa.googlegro ups.com...
> I'm using Merge replication on a table with an autoidentity field. All
> users are entering data at the publisher (no records are ever deleted),
> and at the subscribers the data is read-only. I'm having a problem
> with records being deleted at the publisher. I'm assuming it has to do
> with a subscriber having problems, but I can't track it down.
> Moving forward (SQL 2005), I want to change the replication type to
> transactional. Could someone please tell me the best type of
> replication to use in order to prevent records from being deleted at
> the publisher?
> Any help would be greatly appreciated.
> Thanks,
> Amy Marshall
>
|||Amy,
the issue might be caused by compensating changes. Please take a look at :
http://support.microsoft.com/default...&Product=sql2k
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This morning I had 264 conflicts, which I kept to make sure I got the
deleted records back into the table, but this was the first time there
were any conflicts at all for that table. We are not using any join
filters on this table. I was thinking of using transactional
replication, because all entries into the problem table are done at the
publisher, and the inputs into that table are synched to the
subscribers based on a timeframe that is set from an application which
determines sync times. (Most users set synch times anywhere from 60
minutes - 240 minutes).
What's weird, is that there is a table that is in the same merge
package, that links to an ID in the problem table, but those records
are NEVER deleted at the publisher (no foreign key constraint exists
between the 2 tables). This one table seems to have the problem, and
while monitoring replication, I rarely ever see problems with
subscribers and this package.
Thanks for your input.
Amy

Deleteing rows

I have to delete 30K rows that were inserted into the orders table by
mistake.
The orderid field is used as a FK on many other tables so the delete takes
forever.
Is there any way to increase the performance of this delete?
In this case I know there will not be any associated FKs that reference
these orderids in other tables because the insert I am seeking to undo was
made only to the orders table. Therefore no orphans will be produced (i.e.,
no RI violation) upon delete.
Is it possible to perform the delete without constraint checking? If not,
is there anything I can do to speed up the process?
ThanksDo you have indexes on the FK columns in the referencing tables? That can
speed up such an operation significantly.
You can disable the FK constraint (see ALTER TABLE), but that disabling
applies for all connections, so make sure you are alone on the database
while doing so, if you want to take that route.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David F" <davef@.nksj.ru> wrote in message
news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I have to delete 30K rows that were inserted into the orders table by
> mistake.
> The orderid field is used as a FK on many other tables so the delete
takes
> forever.
> Is there any way to increase the performance of this delete?
> In this case I know there will not be any associated FKs that reference
> these orderids in other tables because the insert I am seeking to undo was
> made only to the orders table. Therefore no orphans will be produced
(i.e.,
> no RI violation) upon delete.
> Is it possible to perform the delete without constraint checking? If not,
> is there anything I can do to speed up the process?
> Thanks
>|||Thanks Tibor.
So it looks like I will not have to DROP the FK, just disable and then
reenable like:
--disable FK
ALTER TABLE child NOCHECK CONSTRAINT fk_id
--re-enable FK
ALTER TABLE child CHECK CONSTRAINT fk_id
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Do you have indexes on the FK columns in the referencing tables? That can
> speed up such an operation significantly.
> You can disable the FK constraint (see ALTER TABLE), but that disabling
> applies for all connections, so make sure you are alone on the database
> while doing so, if you want to take that route.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David F" <davef@.nksj.ru> wrote in message
> news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> takes
was
> (i.e.,
not,
>|||Yes, but did you check the indexes first? Having indexes on a FK column is
often crucial for reasonable performance when doing update and delete in the
referenced table. And not only that, these indexes can help your join
operations significantly (you often join over primary key - foreign key
relationships).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dave" <dave@.nodomain.tv> wrote in message
news:eZjbGj6AEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor.
> So it looks like I will not have to DROP the FK, just disable and then
> reenable like:
> --disable FK
> ALTER TABLE child NOCHECK CONSTRAINT fk_id
> --re-enable FK
> ALTER TABLE child CHECK CONSTRAINT fk_id
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
can
reference
> was
> not,
>

Deleteing rows

I have to delete 30K rows that were inserted into the orders table by
mistake.
The orderid field is used as a FK on many other tables so the delete takes
forever.
Is there any way to increase the performance of this delete?
In this case I know there will not be any associated FKs that reference
these orderids in other tables because the insert I am seeking to undo was
made only to the orders table. Therefore no orphans will be produced (i.e.,
no RI violation) upon delete.
Is it possible to perform the delete without constraint checking? If not,
is there anything I can do to speed up the process?
ThanksDo you have indexes on the FK columns in the referencing tables? That can
speed up such an operation significantly.
You can disable the FK constraint (see ALTER TABLE), but that disabling
applies for all connections, so make sure you are alone on the database
while doing so, if you want to take that route.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David F" <davef@.nksj.ru> wrote in message
news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I have to delete 30K rows that were inserted into the orders table by
> mistake.
> The orderid field is used as a FK on many other tables so the delete
takes
> forever.
> Is there any way to increase the performance of this delete?
> In this case I know there will not be any associated FKs that reference
> these orderids in other tables because the insert I am seeking to undo was
> made only to the orders table. Therefore no orphans will be produced
(i.e.,
> no RI violation) upon delete.
> Is it possible to perform the delete without constraint checking? If not,
> is there anything I can do to speed up the process?
> Thanks
>|||Thanks Tibor.
So it looks like I will not have to DROP the FK, just disable and then
reenable like:
--disable FK
ALTER TABLE child NOCHECK CONSTRAINT fk_id
--re-enable FK
ALTER TABLE child CHECK CONSTRAINT fk_id
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Do you have indexes on the FK columns in the referencing tables? That can
> speed up such an operation significantly.
> You can disable the FK constraint (see ALTER TABLE), but that disabling
> applies for all connections, so make sure you are alone on the database
> while doing so, if you want to take that route.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David F" <davef@.nksj.ru> wrote in message
> news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > I have to delete 30K rows that were inserted into the orders table by
> > mistake.
> >
> > The orderid field is used as a FK on many other tables so the delete
> takes
> > forever.
> >
> > Is there any way to increase the performance of this delete?
> >
> > In this case I know there will not be any associated FKs that reference
> > these orderids in other tables because the insert I am seeking to undo
was
> > made only to the orders table. Therefore no orphans will be produced
> (i.e.,
> > no RI violation) upon delete.
> >
> > Is it possible to perform the delete without constraint checking? If
not,
> > is there anything I can do to speed up the process?
> >
> > Thanks
> >
> >
>|||Yes, but did you check the indexes first? Having indexes on a FK column is
often crucial for reasonable performance when doing update and delete in the
referenced table. And not only that, these indexes can help your join
operations significantly (you often join over primary key - foreign key
relationships).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dave" <dave@.nodomain.tv> wrote in message
news:eZjbGj6AEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor.
> So it looks like I will not have to DROP the FK, just disable and then
> reenable like:
> --disable FK
> ALTER TABLE child NOCHECK CONSTRAINT fk_id
> --re-enable FK
> ALTER TABLE child CHECK CONSTRAINT fk_id
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> > Do you have indexes on the FK columns in the referencing tables? That
can
> > speed up such an operation significantly.
> >
> > You can disable the FK constraint (see ALTER TABLE), but that disabling
> > applies for all connections, so make sure you are alone on the database
> > while doing so, if you want to take that route.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "David F" <davef@.nksj.ru> wrote in message
> > news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > > I have to delete 30K rows that were inserted into the orders table by
> > > mistake.
> > >
> > > The orderid field is used as a FK on many other tables so the delete
> > takes
> > > forever.
> > >
> > > Is there any way to increase the performance of this delete?
> > >
> > > In this case I know there will not be any associated FKs that
reference
> > > these orderids in other tables because the insert I am seeking to undo
> was
> > > made only to the orders table. Therefore no orphans will be produced
> > (i.e.,
> > > no RI violation) upon delete.
> > >
> > > Is it possible to perform the delete without constraint checking? If
> not,
> > > is there anything I can do to speed up the process?
> > >
> > > Thanks
> > >
> > >
> >
> >
>

DeletedFlag field and unique keys

I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||

A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:

ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.

I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.

The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.

DeletedFlag field and unique keys

I was thinking of using a deleted flag rather then deleting a record so that history (auditing, etc.) could be maintained. In certain tables I would like to preserve a unique key on a field (say name or code) of all non-deleted records. Is there any way to do this. I cannot have a Code + DeletedFlag field because there may be multiple records that have been deleted with the same code. I also don't want to include the deleted "codes" in the unique key because I want the user to be able to see the codes they can't use without viewing the deleted records.May be Code+DeletedFlag+DeletedDateTime could be good idea|||That means there could be duplicate codes for active entries which is what I am trying to avoid.|||You could always keep a separate table for the deleted items (with _deleted at the end of the name, or a similar convention). I do this now and then. The deleted items table wouldn't have the uniqueness constraints on it, and you could still use a view to look at the combined tables. Then slap an AFTER DELETE trigger on the original table to automatically move rows into the deleted items table.
|||

A 'better' way to handle archiving is to have separate archiving tables. Same schema, no IDENTITY fields, no constraints, index only on PK, a couple of additional columns:

ChangeBy varchar(50)DEFAULT system_user ChangeDate datetime DEFAULT getdate()|||I have thought about another table (deleted or archived) for records and that probably is the most sensible way to deal with this issue. Ideally I wanted to create an audit table and for any table I wanted to audit I would have a join table (transaction_audit for example). The audit record would hold time, user, action (cud), ip, etc. This way when looking at any record from an audited table you could see a list of actions that were performed on it. Maybe not the complete history (every changed value) which might be overkill but who did what (in general) to it when and from where. Deleted records would be kept in the database in their last state so that activity would be shown as activity and not masked.|||I agree with Arnie.

I do this all the time. Create a table exactly like the original with no identity or constraints with a few extra fields on the end, like ChangeType, UserID, ChangeDateTime, called tablename_audit. Then setup a trigger to "insert into table_audit select *, "D", @.userid, getdate() from deleted" the audit table.

The only thing you need to remember with this is, when you change the original, you MUST also change the fields in the audit log to match. This can take some time, if you have many audit records.

deleted object for trigger

Is 'deleted' object available for a table with Identity field? I try the
scripts as following. It gets me the error "Invalid object name 'deleted'".
How can I bypass it? Thanks.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
GO
INSERT dbo.myTable_History SELECT * FROM deleted
GO
SET IDENTITY_INSERT dbo.myTable_History OFF
GOGO terminates batches in Query Analyzer. So your trigger does nothing
more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
should work...
That said, why does your table have an IDENTITY column if you're just
bypassing it from the trigger anyway?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> Is 'deleted' object available for a table with Identity field? I try the
> scripts as following. It gets me the error "Invalid object name
'deleted'".
> How can I bypass it? Thanks.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> GO
> INSERT dbo.myTable_History SELECT * FROM deleted
> GO
> SET IDENTITY_INSERT dbo.myTable_History OFF
> GO
>|||The GO keyword should be only at the end of the trigger, not after each
statement.
To use SET IDENTITY_INSERT you must specify the columns (it doesn't
work with *)
Razvan|||1.
I removed all GO statement. Now the trigger is:
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History SELECT * FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
It still doesn't work though. The error shows:
Error 8101: An explicit value for the identity column in ... can only be
specified when a columne list is used and IDENTITY_INSERT is ON.
Do I miss anything?
2.
You raised a good quesiton. The reason I have IDENTITY field on the history
tables is just because they are created in the SQL script by
SELECT * INTO MyTable1_History FROM MyTable1
SELECT * INTO MyTable2_History FROM MyTable2
............
Since there is no short cut to change table's IDENTITY field to be plain int
field, we keep the IDENTITY field in the history table.
"Adam Machanic" wrote:

> GO terminates batches in Query Analyzer. So your trigger does nothing
> more than SET IDENTITY_INSERT ON for the table. Remove the GOs and it
> should work...
> That said, why does your table have an IDENTITY column if you're just
> bypassing it from the trigger anyway?
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:83E83E75-16E6-4473-B3AB-BEA946A60B08@.microsoft.com...
> 'deleted'".
>
>|||Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you must
specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
"Razvan Socol" wrote:

> The GO keyword should be only at the end of the trigger, not after each
> statement.
> To use SET IDENTITY_INSERT you must specify the columns (it doesn't
> work with *)
> Razvan
>|||A) Use a column list
B) Stop being lazy and create your tables using Data Definition Language.
Why would you take a shortcut that doesn't save much time and is going to
make your database worse?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:BBCE9EF9-1D63-4947-9B38-3A2F96195A3C@.microsoft.com...
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the
history
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain
int
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>
nothing
just
the|||You have to write the column list.
CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
FOR UPDATE
AS
SET IDENTITY_INSERT dbo.myTable_History ON
INSERT dbo.myTable_History (col1, ..., coln)
SELECT col1,..., coln FROM deleted
SET IDENTITY_INSERT dbo.myTable_History OFF
go

> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
select col2, ..., coln
into t
from table1
alter table t
add col1 int not nul
go
AMB
"Sean" wrote:
> 1.
> I removed all GO statement. Now the trigger is:
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History SELECT * FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> It still doesn't work though. The error shows:
> Error 8101: An explicit value for the identity column in ... can only be
> specified when a columne list is used and IDENTITY_INSERT is ON.
> Do I miss anything?
> 2.
> You raised a good quesiton. The reason I have IDENTITY field on the histor
y
> tables is just because they are created in the SQL script by
> SELECT * INTO MyTable1_History FROM MyTable1
> SELECT * INTO MyTable2_History FROM MyTable2
> ............
> Since there is no short cut to change table's IDENTITY field to be plain i
nt
> field, we keep the IDENTITY field in the history table.
>
> "Adam Machanic" wrote:
>|||Sean
To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
explicitly list all the columns in the table. Please read about the
variations of the INSERT command in the Books Online.
It would be something like this:
INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
SELECT * FROM deleted
One of the columns names needs to be the name of the identity column, in the
right position.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
> Thanks for your reply. What do you mean 'To use SET IDENTITY_INSERT you
> must
> specify the columns'. Shouldn't I SET IDENTITY_INSERT ON to the table?
>
> "Razvan Socol" wrote:
>|||Thanks for the reply. I got it.
"Alejandro Mesa" wrote:
> You have to write the column list.
> CREATE TRIGGER dbo.myTable_Update ON dbo.myTable
> FOR UPDATE
> AS
> SET IDENTITY_INSERT dbo.myTable_History ON
> INSERT dbo.myTable_History (col1, ..., coln)
> SELECT col1,..., coln FROM deleted
> SET IDENTITY_INSERT dbo.myTable_History OFF
> go
>
> select col2, ..., coln
> into t
> from table1
> alter table t
> add col1 int not nul
> go
>
> AMB
> "Sean" wrote:
>|||Thanks, Kalen.
"Kalen Delaney" wrote:

> Sean
> To use INSERT to put rows in a table with INDENTITY_INSERT ON, you must
> explicitly list all the columns in the table. Please read about the
> variations of the INSERT command in the Books Online.
> It would be something like this:
> INSERT dbo.myTable_History (name_of_column1, name_of_column_2, ...)
> SELECT * FROM deleted
> One of the columns names needs to be the name of the identity column, in t
he
> right position.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Sean" <Sean@.discussions.microsoft.com> wrote in message
> news:3A42CE27-B412-424B-9A12-35882AB63F4F@.microsoft.com...
>
>

Delete Trigger

I'm trying to get a record added to clsrecord_accessed
when deleting (as an example) notes from the LONGNOTES
field. This isn't appearing to happen though with the
below code. What am I missing?
CREATE TRIGGER Prescott_Delete_ACTIVITY
ON sysdba.ACTIVITY
FOR DELETE
AS
IF UPDATE (LONGNOTES)
or UPDATE (DESCRIPTION)
or UPDATE (STARTDATE)
BEGIN
IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
= 'ABENJ0000022')
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Delete' FROM
Deleted D WHERE AccountID = 'ABENJ0000022'
ENDCheck out BOL for "create trigger". IF UPDATE() is not useful in a delete
trigger since columns are "mentioned" in the delete statement.
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||> columns are "mentioned" in the delete statement.
Did you mean "not mentioned"?|||so I change it to this, and still nothing:
CREATE TRIGGER Prescott_Update_ACTIVITY
ON sysdba.ACTIVITY
FOR Insert, Update, Delete
AS
IF NOT EXISTS (SELECT * FROM Inserted I WHERE I.AccountID
= 'ABENJ0000022'
IF EXISTS (SELECT * FROM Deleted I WHERE I.AccountID
= 'ABENJ0000022'
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Inserted I on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change' FROM
Inserted I
ELSE
IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted' FROM
Deleted D
GO
>--Original Message--
>Check out BOL for "create trigger". IF UPDATE() is not
useful in a delete
>trigger since columns are "mentioned" in the delete
statement.
>"CalTab" <anonymous@.discussions.microsoft.com> wrote in
message
>news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>> = 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>> END
>
>.
>|||CalTab (anonymous@.discussions.microsoft.com) writes:
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>= 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
I took the liberty to reformat your trigger body, to make it more
readable:
1) IF NOT EXISTS (SELECT * FROM Inserted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
2) IF EXISTS (SELECT * FROM Deleted I
WHERE I.AccountID = 'ABENJ0000022')
BEGIN
3) IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Inserted I
on CA.CLSRECORD_ACCESSEDID = I.ACTIVITYID)
BEGIN
A) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, REATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE(), I.CONTACTID,
I.ACCOUNTID, 'Activity Change'
FROM Inserted I
END
ELSE IF NOT EXISTS (SELECT *
FROM CLSRECORD_ACCESSED CA
JOIN Deleted D
on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
BEGIN
B) INSERT CLSRECORD_ACCESSED(
CLSRECORD_ACCESSEDID, CREATEUSER, CREATEDATE, CONTACTID,
ACCOUNTID, RECORD_TYPE)
SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
D.ACCOUNTID, 'Activity Deleted'
FROM Deleted D
END
END
END
So if this code is trigger by a DELETE, we will pass the first IF
statement, because Inserted is empty in a DELETE trigger. If the account
is the right one, we will pass the second IF statement too. And we
will always pass the third IF statement, because Inserted is empty.
This means that we will execute INSERT statement A, but since Inserted
is empty, nothing will happen.
The test in the ELSE IF statement is never executed, and neither is
INSERT statement B, when trigger is fired by DELETE.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Uh... why do you have hard-coded conditionals in your trigger? Wouldn't it
make more sense to put these values somewhere instead of hard-coding values?
Why are you using the inserted table (it is not populated by a DELETE)?
"CalTab" <anonymous@.discussions.microsoft.com> wrote in message
news:031901c3d172$0fce96a0$a301280a@.phx.gbl...
> I'm trying to get a record added to clsrecord_accessed
> when deleting (as an example) notes from the LONGNOTES
> field. This isn't appearing to happen though with the
> below code. What am I missing?
> CREATE TRIGGER Prescott_Delete_ACTIVITY
> ON sysdba.ACTIVITY
> FOR DELETE
> AS
> IF UPDATE (LONGNOTES)
> or UPDATE (DESCRIPTION)
> or UPDATE (STARTDATE)
> BEGIN
> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
> = 'ABENJ0000022')
> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA JOIN
> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Delete' FROM
> Deleted D WHERE AccountID = 'ABENJ0000022'
> END|||so, is this code useable?
>--Original Message--
>CalTab (anonymous@.discussions.microsoft.com) writes:
>> I'm trying to get a record added to clsrecord_accessed
>> when deleting (as an example) notes from the LONGNOTES
>> field. This isn't appearing to happen though with the
>> below code. What am I missing?
>> CREATE TRIGGER Prescott_Delete_ACTIVITY
>> ON sysdba.ACTIVITY
>> FOR DELETE
>> AS
>> IF UPDATE (LONGNOTES)
>> or UPDATE (DESCRIPTION)
>> or UPDATE (STARTDATE)
>> BEGIN
>> IF EXISTS (SELECT * FROM Deleted D WHERE D.AccountID
>>= 'ABENJ0000022')
>> IF NOT EXISTS (SELECT * FROM CLSRECORD_ACCESSED CA
JOIN
>> Deleted D on CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
>> INSERT INTO CLSRECORD_ACCESSED(CLSRECORD_ACCESSEDID,
>> CREATEUSER, CREATEDATE, CONTACTID, ACCOUNTID,
RECORD_TYPE)
>> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE(),
D.CONTACTID,
>> D.ACCOUNTID, 'Activity Delete' FROM
>> Deleted D WHERE AccountID = 'ABENJ0000022'
>I took the liberty to reformat your trigger body, to
make it more
>readable:
>1) IF NOT EXISTS (SELECT * FROM Inserted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>2) IF EXISTS (SELECT * FROM Deleted I
> WHERE I.AccountID = 'ABENJ0000022')
> BEGIN
>3) IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED CA
> JOIN Inserted I
> on CA.CLSRECORD_ACCESSEDID =I.ACTIVITYID)
> BEGIN
>A) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, REATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT I.ACTIVITYID, I.MODIFYUSER, GETDATE
(), I.CONTACTID,
> I.ACCOUNTID, 'Activity Change'
> FROM Inserted I
> END
> ELSE IF NOT EXISTS (SELECT *
> FROM CLSRECORD_ACCESSED
CA
> JOIN Deleted D
> on
CA.CLSRECORD_ACCESSEDID = D.ACTIVITYID)
> BEGIN
>B) INSERT CLSRECORD_ACCESSED(
> CLSRECORD_ACCESSEDID, CREATEUSER,
CREATEDATE, CONTACTID,
> ACCOUNTID, RECORD_TYPE)
> SELECT D.ACTIVITYID, D.MODIFYUSER, GETDATE
(), D.CONTACTID,
> D.ACCOUNTID, 'Activity Deleted'
> FROM Deleted D
> END
> END
> END
>
>So if this code is trigger by a DELETE, we will pass the
first IF
>statement, because Inserted is empty in a DELETE
trigger. If the account
>is the right one, we will pass the second IF statement
too. And we
>will always pass the third IF statement, because
Inserted is empty.
>This means that we will execute INSERT statement A, but
since Inserted
>is empty, nothing will happen.
>The test in the ELSE IF statement is never executed, and
neither is
>INSERT statement B, when trigger is fired by DELETE.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/boo
ks.asp
>.
>|||AnonymousWheel (anonymous@.discussions.microsoft.com) writes:
> so, is this code useable?
I don't if you are the person who asked the original question, or someone
else.
Obviously, the trigger code in its current form does not seem to be
extremely useful, since it does not perform the intended task.
But I can't tell how from the mark it is, since I don't know the
business requirements.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp|||Oops - yes, exactly.
"Foo Man Chew" <foo@.man.chew> wrote in message
news:uFsfwZX0DHA.4060@.TK2MSFTNGP11.phx.gbl...
> > columns are "mentioned" in the delete statement.
> Did you mean "not mentioned"?
>|||all we're trying to do is whenever there is an
Insert,update or delete in the activity table, we'd like a
row inserted in clsrecord_accessed to produce an "audit"
of the activity.
>--Original Message--
>AnonymousWheel (anonymous@.discussions.microsoft.com)
writes:
>> so, is this code useable?
>I don't if you are the person who asked the original
question, or someone
>else.
>Obviously, the trigger code in its current form does not
seem to be
>extremely useful, since it does not perform the intended
task.
>But I can't tell how from the mark it is, since I don't
know the
>business requirements.
>--
>Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
>Books Online for SQL Server SP3 at
>http://www.microsoft.com/sql/techinfo/productdoc/2000/book
s.asp
>.
>

Delete row where a particular field value is not in a list

I have a table which has a field called filename, thus the data in the field would look something like this:

FileName.doc
FileName.txt
FileName.foo

I need to delete several rows from this table where the filename field DOES NOT contain the following extensions (e.g. row with FileName.foo would be deleted):

.txt
.pdf
.rtf
.doc
.htm
.html
.mht
.mhtml
.dot
.wk1
.wk3
.wk4
.xls
.xlw
.asc
.olk
.pab
.scd
.ans
.wri
.mcw
.wpd
.wps
.ppt
.pps
.xls
.xlt
.xlw

Crazy method for identifing file extension:

declare @.filename varchar(100)

set @.filename= 'filename.txt'

select charindex('.',@.filename,-1) --Position of last dot in @.filename

select substring(@.filename,charindex('.',@.filename,-1),len(@.filename)-charindex('.',@.filename,-1)+1)

--result '.txt'

Than you could use "IN keyword"

PS. With query works very slow

|||

create table mytable(filename varchar(20))
insert into mytable(filename) values('FileName.doc')
insert into mytable(filename) values('FileName.txt')
insert into mytable(filename) values('FileName.foo')

create table allowed(ext varchar(8))
insert into allowed(ext)
select '.txt' union all
select '.pdf' union all
select '.rtf' union all
select '.doc' union all
select '.htm' union all
select '.html' union all
select '.mht' union all
select '.mhtml' -- etc

delete from mytable
where not exists(select * from allowed where filename like '%'+ext)

|||Marks answer seems most appropriate because it allows me to keep a table of "allowable" extensions. But I am open to other ideas if anyone else wants to contribute.

Delete relationship?

How do I delete a relationship between tables in SQL Server 7.0?
I previously had a relationship between two tables, but I renamed the field
and table of one of the tables and the old relationship still points to the
old field name and table. How do I remove the defunct relationship?
It doesn't show in my diagram because the old table no longer exists (well
it does but it's been renamed).Referential integrity between tables is implemented using constraints. Try
this:
alter table <table_name> drop constraint <constraint_name>
"Steve" <steve@.hello.com> wrote in message news:43301ef3_1@.x-privat.org...
> How do I delete a relationship between tables in SQL Server 7.0?
> I previously had a relationship between two tables, but I renamed the
> field and table of one of the tables and the old relationship still points
> to the old field name and table. How do I remove the defunct relationship?
> It doesn't show in my diagram because the old table no longer exists (well
> it does but it's been renamed).
>|||ALTER TABLE tblname DROP CONSTRAINT constraintname
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steve" <steve@.hello.com> wrote in message news:43301ef3_1@.x-privat.org...
> How do I delete a relationship between tables in SQL Server 7.0?
> I previously had a relationship between two tables, but I renamed the field
> and table of one of the tables and the old relationship still points to the
> old field name and table. How do I remove the defunct relationship?
> It doesn't show in my diagram because the old table no longer exists (well
> it does but it's been renamed).
>