By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,602 Members | 1,473 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,602 IT Pros & Developers. It's quick & easy.

Using a trigger on a view

P: n/a
I have never written a trigger before, but I have a couple of uses for
them now so I am trying. I have a view that I created and I want to
have this trigger, run anytime the view is run. I don't know if my
problem is with the trigger or permissions on the view, right now I
have given everyone permission to select and update on the view (just
trying to figure this out). Here is the begining of my trigger.

use OTB
If Exists (Select name from sysobjects
Where name='trg_otb_update_catalog_udf' and type='TR')
Drop Trigger trg_otb_update_catalog_udf
Go
Create Trigger trg_otb_update_catalog_udf
On vw_OTB_catalog
For Insert, update
as
Update ar_cust_udf
set part_catalog=c1.parts_catalog
etc...

Everything in the update statement to the end of the trigger works as
I intended. However when I run this it will not create the trigger,
instead it errors and returns the following:

Server: Msg 208, Level 16, State 4, Procedure
trg_otb_update_catalog_udf, Line 1
Invalid object name 'vw_OTB_catalog'.

However this is the correct view name.
any ideas?
Thanks,
Mike
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 15 Apr 2004 08:41:20 -0700, Mike wrote:
I have never written a trigger before, but I have a couple of uses for
them now so I am trying. I have a view that I created and I want to
have this trigger, run anytime the view is run. I don't know if my
problem is with the trigger or permissions on the view, right now I
have given everyone permission to select and update on the view (just
trying to figure this out). Here is the begining of my trigger.

use OTB
If Exists (Select name from sysobjects
Where name='trg_otb_update_catalog_udf' and type='TR')
Drop Trigger trg_otb_update_catalog_udf
Go
Create Trigger trg_otb_update_catalog_udf
On vw_OTB_catalog
For Insert, update
as
Update ar_cust_udf
set part_catalog=c1.parts_catalog
etc...

Everything in the update statement to the end of the trigger works as
I intended. However when I run this it will not create the trigger,
instead it errors and returns the following:

Server: Msg 208, Level 16, State 4, Procedure
trg_otb_update_catalog_udf, Line 1
Invalid object name 'vw_OTB_catalog'.

However this is the correct view name.
any ideas?


Hi Mike,

Let's just say that Microsoft can be nominated for the most misleading
error message of the year, okay?

The only type of triggers defined on a view are "instead of" triggers.
Change "For Insert, update" to "Instead of Insert, update" and this
error message will go away.

Remember - if an instead of trigger is defined, SQL Server will not
attempt to change any data in the base tables. You will have to make
sure that the trigger will apply the changes the user expects from his
of her data modification statement.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2

P: n/a
Ok, so say I want a piece of SQL code to run everytime the view is
accessed, even if no changes were made. Is there a way to do this that
I am not think of either through this trigger or some other way in SQL?

I realize that I could set up a batch file or write a program that would
accomplish this, but that really isn't what I am looking for. I want to
be able to use this view in a report, and then have it run the update
statement automatically without further end user intervention.

I am just trying to make a 2 step process into 1 step for the end user.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3

P: n/a
On 15 Apr 2004 20:57:14 GMT, Michael Rea wrote:
Ok, so say I want a piece of SQL code to run everytime the view is
accessed, even if no changes were made. Is there a way to do this that
I am not think of either through this trigger or some other way in SQL?

I realize that I could set up a batch file or write a program that would
accomplish this, but that really isn't what I am looking for. I want to
be able to use this view in a report, and then have it run the update
statement automatically without further end user intervention.

I am just trying to make a 2 step process into 1 step for the end user.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


Hi Michael,

It is possible to fire a trigger each time a delete, insert or update
statement is executed against a view, even if no actual modifications
were made. (A delete or update with no rows matching the where cluase,
an insert .. select with the select returning an empty result set or
an update that sets columns to the value they already had).

It is however NOT possible to fire a trigger on a select against a
view (or a table, for taht matter). Triggers are designed to fire on
data *modification* statements.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.