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_name sysname, @.msg varchar(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.This is a multi-part message in MIME format.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
You cannot create a FOR trigger (now known as an AFTER trigger) on a =view. You can create an INSTEAD OF trigger on a view, however.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Roust_m" <roustam@.hotbox.ru> wrote in message =news:a388fd78.0309300241.4462ca59@.posting.google.com...
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_name sysname, @.msg varchar(3000) select @.user_name =3D name from deleted
set @.msg =3D '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.
--=_NextPart_000_0012_01C3872D.EA0155E0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
You cannot create a FOR trigger (now =known as an AFTER trigger) on a view. You can create an INSTEAD OF trigger on =a view, however.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Roust_m"
wrote in message news:a388fd=78.0309300241.4462ca59@.posting.google.com...Hi!I have a view defined in MANAGE database as:CREATE VIEW dbo.sysdatabasesview ASSELECT *FROM master.dbo.sysdatabases WITH (nolock)GOTrying to place a =trigger on it:CREATE TRIGGER sysdatabasesview$onDelete ON [dbo].[sysdatabasesview] FOR DELETE AS Declare @.user_name =sysname, @.msg varchar(3000) select @.user_name =3D name from deleted =set @.msg =3D '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.
--=_NextPart_000_0012_01C3872D.EA0155E0--sql
