Trigger on view does not work.

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

Transact-SQL (2000)

Trigger on view does not work.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-28 : 06:53:02

Geert writes "Hi there,

I am stuck with a problem.
In the books online of SQL Server there is an article where the use of Instead of Triggers for views is explained. I tried to use the example explained in this article to change this and really use it. Unfortunately, this example does not work on my server. According to my knowledge the following small example should work:

I have one table Person as:
CREATE TABLE [tblPerson] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NOT NULL ,
[Updated] [char] (10) NULL ,
CONSTRAINT [PK_tblPerson] PRIMARY KEY CLUSTERED
(
[ID]
) ON [PRIMARY]
) ON [PRIMARY]

And a view on this table as:
SELECT ID, Name, Updated
FROM dbo.tblPerson

I want to update the field 'Updated' after an UPDATE statement using a trigger. On the table tblPerson I create this trigger:

CREATE TRIGGER [trgInsteadOfUpdate] ON dbo.tblPerson
INSTEAD OF UPDATE
AS

UPDATE tblPerson
SET Updated = 'Yes', tblPerson.[Name] = inserted.[Name]
FROM inserted

And this works the way I want it to. But when I try to create the same trigger, but now on the view, the trigger doesn't fire anymore!!!

Hope anybody can help.

Greetings.
Geert"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-08-28 : 07:19:13

Which are you updating, the table or the view?

Jay White
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources