On delete trigger for a view

Question

Post reply

On delete trigger for a view

Roust_m

SSCoach

Points: 17410

September 30, 2003 at 4:05 am

#82969

Hi!

I have a view defined in MANAGE database as:

 

CREATE VIEW dbo.sysdatabasesview

AS

SELECT     *

FROM         master.dbo.sysdatabases WITH (nolock)

GO

Trying to place a trigger on it:

 

CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] 

FOR DELETE 

AS

 Declare

@user_namesysname,

@msgvarchar(3000)



 select @user_name = name from deleted

 set @msg = 'Delete database  ' + @user_name + ' on server ' + @@servername + ' from host ' + host_name()

 

 insert into MANAGE..MAIL (recipient, subject, message, occur)

values ('myemail@domain.local', 'Delete datadase', @msg, getdate())

Get an error:

 

Error 208: Invalid object name 'dbo.sysdatabasesview'

What is wrong?

Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1

Triggers on views must be INSTEAD OF, not FOR. But there is no useful purpose for the trigger you are creating. One does not drop a database by deleting it from sysdatabases or an updatable view on sysdatabases.

--Jonathan

Roust_m SSCoach Points: 17410 More actions · Answer 2

quote:
...One does not drop a database by deleting it from sysdatabases or an updatable view on sysdatabases.

- But deleting a record from sysdatabases is part of process of database deletion, or not?

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
quote:
...One does not drop a database by deleting it from sysdatabases or an updatable view on sysdatabases.
- But deleting a record from sysdatabases is part of process of database deletion, or not?

Sure, but the process does not use your view.

--Jonathan