Create triggers on a view - SQL Server Forums

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)

Create triggers on a view

Author

Topic

irislaw
Starting Member

7 Posts

Posted - 2005-02-18 : 16:46:48

Do you know if I can create a trigger on a view? Whenever a record is inserted into a view, can I insert a new record into a table?
Actually, I tried the following scripts, but, it only works on a table, not on a view. I got error after running the following scripts:

{ Server: Msg 208, Level 16, State 4, Procedure userTrigger, Line 1
Invalid object name 'Userview'. }

I checked in sysobjects, it did exist.That’s why I donno what the problem is.

Anyone know how to do it? Thanks!

CREATE TRIGGER userTrigger

ON Userview /* the name of the view */ /* If I put table name here, it works */

For Insert

AS

DECLARE @userID VARCHAR(11)
DECLARE @lastName VARCHAR(25)
DECLARE @Title VARCHAR(25)

SELECT @userID = (SELECT userID FROM Inserted)
select @LastName = 'NULL'
select @Title = 'NULL'

INSERT EmployeesTest values (@LastName,@userID,@Title)

Hippi
Yak Posting Veteran

63 Posts

Posted - 2005-02-18 : 20:41:50

I think you should read about INSTEAD OF trigger from BOL

Kristen
Test

22859 Posts

Posted - 2005-02-19 : 04:24:51

Although beware that INSTEAD OF TRIGGERS on VIEWs can be a pain ... you starting having to have ARITH_ABORT in the right state when you query them and in my experience all sorts of other grief ensues ...

Kristen

irislaw
Starting Member

7 Posts

Posted - 2005-02-22 : 17:29:47

I tried to use Instead of, but, I cannot see the row that I inserted into this view: "UserView". Is anything wrong with the following script?

CREATE TRIGGER userTrigger
ON Userview
Instead of Insert
AS

begin

DECLARE @userID VARCHAR(11)
DECLARE @lastName VARCHAR(25)
DECLARE @Title VARCHAR(25)

SELECT @userID = (SELECT userID FROM Inserted)
select @LastName = 'NULL'
select @Title = 'NULL'

INSERT into EmployeesTest values (@LastName,@userID,@Title)

end

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-02-22 : 18:07:17

quote:

Although beware that INSTEAD OF TRIGGERS on VIEWs can be a pain ... you starting having to have ARITH_ABORT in the right state when you query them and in my experience all sorts of other grief ensues ...

I am unaware of ARITH_ABORT having any impact on INSTEAD OF triggers. INDEXED Views is another story... In my current project I use them extensively for ONE-to-ONE RI implementation and they work like a charm. The only painful part is I have cascading update/delete between the tables with a natural key ie. The view can perform set inserts but only single row updates...

DavidM

A front-end is something that tries to violate a back-end.

Kristen
Test

22859 Posts

Posted - 2005-02-23 : 00:20:28

irislaw: You need to build your trigger so that it works with multiple rows in "inserted"


CREATE TRIGGER userTrigger
ON Userview
Instead of Insert
AS

begin

INSERT into EmployeesTest
SELECT  NULL,  -- Last name
        userID,
        NULL   -- title
FROM Inserted

end

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources