Using a trigger on a view

[Mike]

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

[Hugo Kornelis]

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)

[Michael Rea]

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!

[Hugo Kornelis]

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)