delete triggers

sql2k sp3
I assumed this would work as easily as the Insert and Update Triggers I
wrote did:
create trigger DelAuthors
on authors
for delete
as
begin
set nocount on
delete tmp_authors
where tmp_authors.au_id = deleted.au_id
end
Server: Msg 107, Level 16, State 2, Procedure DelAuthors, Line 7
The column prefix 'deleted' does not match with a table name or alias name
used in the query.
Heres the schema for tmp_authors(and Authors):
CREATE TABLE [dbo].[tmp_authors] (
[au_id] [id] NOT NULL ,
[au_lname] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[au_fname] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[phone] [char] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[address] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[state] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[zip] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[contract] [bit] NOT NULL
) ON [PRIMARY]
GO
Help is appreciated.
TIA, ChrisRChange your DELETE statement to:
DELETE FROM tmp_authors
WHERE EXISTS
(SELECT *
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id)
That's the ANSI Standard update syntax. SQL Server also supports Microsoft
own proprietary version of the DELETE statement using joins and which,
bizarely, requires an extra FROM clause to do what you are attempting.
DELETE
FROM tmp_authors
FROM deleted
WHERE tmp_authors.au_id = deleted.au_id
David Portas
SQL Server MVP
--|||Thanks David.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:ZLWdnU8W65uGbv_fRVn-tw@.giganews.com...
> Change your DELETE statement to:
> DELETE FROM tmp_authors
> WHERE EXISTS
> (SELECT *
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id)
> That's the ANSI Standard update syntax. SQL Server also supports Microsoft
> own proprietary version of the DELETE statement using joins and which,
> bizarely, requires an extra FROM clause to do what you are attempting.
> DELETE
> FROM tmp_authors
> FROM deleted
> WHERE tmp_authors.au_id = deleted.au_id
> --
> David Portas
> SQL Server MVP
> --
>

Delete large amount of records

Hi,
I need to delete large amount of record from sql2k table wly.
My question is that there is any way that I could delete them pypassing sql
log file.
Delete * from table1 where year(createdate) < '02'
Thanks,no, DELETE is a logged operation. if the majority of rows in the table is to
be deleted, it might be faster to move the remaining rows into a new table,
truncate the original table (truncate is non-logged) and then re-insert, or
drop the original table alltogether and rename the new table. another
approach would be to delete smaller portions of data in a loop, one month or
one w at the time.
dean
"mecn" <mecn2002@.yahoo.com> wrote in message
news:eBm8nKOKGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I need to delete large amount of record from sql2k table wly.
> My question is that there is any way that I could delete them pypassing
> sql log file.
> Delete * from table1 where year(createdate) < '02'
> Thanks,
>|||Thanks,
Dean
"Dean" <dvitner@.nospam.gmail.com> wrote in message
news:OK5F05OKGHA.604@.TK2MSFTNGP14.phx.gbl...
> no, DELETE is a logged operation. if the majority of rows in the table is
> to be deleted, it might be faster to move the remaining rows into a new
> table, truncate the original table (truncate is non-logged) and then
> re-insert, or drop the original table alltogether and rename the new
> table. another approach would be to delete smaller portions of data in a
> loop, one month or one w at the time.
> dean
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:eBm8nKOKGHA.1312@.TK2MSFTNGP09.phx.gbl...
>