Problem with Create Trigger on View

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem with Create Trigger on View

Author

Topic

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-12 : 02:14:37

Hi,

I'm trying to create a trigger on a view but I get following error:

The request for procedure 'View_1' failed because 'View_1' is a view object.

My view name is dbo.View_1
and my create trigger query is as follows:

CREATE TRIGGER dbo.[trgPersonViewAudit] 
   ON dbo.View_1
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

Could you tell me where I'm wrong?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-12 : 02:21:23

You have to use INSTEAD OF TRIGGER on VIEW

KH
[spoiler]Time is always against us[/spoiler]

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-12 : 02:44:00

Thx Khtan for the reply!

I modified it to:

CREATE TRIGGER dbo.[trgPersonViewAudit] 
 INSTEAD OF  INSERT,DELETE,UPDATE  ON dbo.View_1

but it says:

Incorrect syntax near 'INSTEAD'.

I can't actually find any other format for INSTEAD OF in books!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-12 : 02:48:36

you have to create the instead of trigger separately for each of the INSERT, UPDATE, DELETE operation.

see BOL http://msdn.microsoft.com/en-us/library/ms179288.aspx on INSTEAD OF TRIGGER

KH
[spoiler]Time is always against us[/spoiler]

Peace2007
Posting Yak Master

239 Posts

Posted - 2008-08-12 : 03:11:03

it's resolved thank you Khtan :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources