Need Help with Delete Trigger

[bintipa]

Hi,

I sort of try to manage a SQL Server 2000 database built by
somebody else.
Recently I've had a scare with disappearing data, so I'm now
much more suspicious of users with Delete privleges.

I wanted to create Delete Triggers to tell me Who, When,
and What, and store it in a new table I created.

After most of a day of fiddling with it, consulting texts,
Books Online, forums like this one, and plenty of error messages,
this is what I have so far.....

CREATE TRIGGER DiveInfoLogDel ON [dbo].[DiveInfo]
FOR DELETE
AS
DECLARE @HKEY as int

SELECT @HKEY = [DiveInfo].[HKEY]
FROM [DiveInfo] INNER JOIN deleted ON ([DiveInfo].[HKey] = deleted.[HKey])


INSERT INTO WhoDeleted (DelTable, DelID, DelDate, DelUser)
VALUES ('DiveInfo ' , @HKEY, getdate(), SUSER_SNAME() )

.....So, the When and the Who and the table name get
recorded fine, but the @HKEY variable always comes
through as <Null>.

Is there any kindly soul who could show me how to retrieve
and save the record identifier HKey value ?
Thanks in advance.

[skhanal]

Is DiveOctopusBioInfo schema owner for the table HKEY?

Also once the record is deleted from DiveInfo, the join may return 0 rows, so instead use this

INSERT INTO WhoDeleted (DelTable, DelID, DelDate, DelUser)
SELECT 'DiveInfo ',deleted.[HKEY], getdate(), SUSER_SNAME()
FROM deleted

[bintipa]

Hi skhanal,

Sorry for the confusion, I'd wanted to edit my stuff for readability,
and missed one instance of "DiveOctopusBioInfo" --> "DiveInfo".
Fixed it now.

As for your suggestion, yes I've tried using the "deleted" logical
table result rather than my own table, had the same result,
Null value.

????.

[bintipa]

Hi again skhanal,

I must have been confused yesterday (wouldn't be the first time),
tried your suggestion again today and it worked.

Happy happy happy.

THANK YOU VERY MUCH.