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
> --
>