Enterprise Manager vs. INSTEAD OF triggers?

Hello all, i seem to have run into something weird...
I have a view which joins several tables (one main, and several relational
lookups). The view is simple-updateable, but i overrode this with an INSTEAD
OF UPDATE trigger to prevent people from changing the key fields, or the
lookup values. This works fine from Query Analyzer, or Access. If i try to
update one of the other fields, it simply doesnt take but the fields which
do get handled by the trigger do... all works fine.
However, if i open the view in Ent. Mgr. and type in changes to one of the
"non-updatable" fields, it actually updates it. It appears to be that when
making changes to data through the Enterprise Manager data views, it
bypasses any triggers (or at least INSTEAD OF triggers).
Is this actually the case? It seems kind of screwey, but that would seem to
be borne out by my little experiments here.
Cheers,
- Arthur.
When you open a view and update the data from EM, the UPDATE statemenet is
issued directly to the table, not to the view. That's why the trigger is
bypassed.
"Arthur Dent" wrote:

> Hello all, i seem to have run into something weird...
> I have a view which joins several tables (one main, and several relational
> lookups). The view is simple-updateable, but i overrode this with an INSTEAD
> OF UPDATE trigger to prevent people from changing the key fields, or the
> lookup values. This works fine from Query Analyzer, or Access. If i try to
> update one of the other fields, it simply doesnt take but the fields which
> do get handled by the trigger do... all works fine.
> However, if i open the view in Ent. Mgr. and type in changes to one of the
> "non-updatable" fields, it actually updates it. It appears to be that when
> making changes to data through the Enterprise Manager data views, it
> bypasses any triggers (or at least INSTEAD OF triggers).
> Is this actually the case? It seems kind of screwey, but that would seem to
> be borne out by my little experiments here.
> Cheers,
> - Arthur.
>
>
|||Arthur Dent wrote:
> Hello all, i seem to have run into something weird...
> I have a view which joins several tables (one main, and several
> relational lookups). The view is simple-updateable, but i overrode
> this with an INSTEAD OF UPDATE trigger to prevent people from
> changing the key fields, or the lookup values. This works fine from
> Query Analyzer, or Access. If i try to update one of the other
> fields, it simply doesnt take but the fields which do get handled by
> the trigger do... all works fine.
> However, if i open the view in Ent. Mgr. and type in changes to one
> of the "non-updatable" fields, it actually updates it. It appears to
> be that when making changes to data through the Enterprise Manager
> data views, it bypasses any triggers (or at least INSTEAD OF
> triggers).
> Is this actually the case? It seems kind of screwey, but that would
> seem to be borne out by my little experiments here.
> Cheers,
> - Arthur.
I'm not seeing that behavior here. I created an instead of update
trigger on a table and SQL EM spits out an error when the trigger is
violated. Here's the code. What do you see when you change a date to
value greater than today?
create table test (col1 int identity not null, col2 datetime not null)
go
insert into test values (getdate())
insert into test values (getdate() - 1)
insert into test values (getdate() - 2)
go
create trigger test_ioi on test
instead of update
as
begin
If Not Exists (Select * from inserted where col2 > getdate())
Update test
Set col2 = i.col2
From test, inserted i
Where test.col1 = i.col1
Else
RAISERROR ('Date provided is greater than today'' date. Update
failed.', 16, 1)
end
go
David Gugick
Imceda Software
www.imceda.com
|||My trigger is on a VIEW, not on a table. Jack answered above though, and
apparently explained why i get this behaviour.
In EM, when you open a view, its not actually opening the view, but the
underlying tables, only using the view definition. So triggers on the view
DO get bypassed.
|||Just an FYI, there is not such thing as "opening" a table or a view. EM does SELECT against the view
but issue the UPDATE/INSERT/DELETE against the table. Check out the WITH VIEW_METADATA option when
you create the view to handle this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Arthur Dent" <hitchhikersguideto-news@.yahoo.com> wrote in message
news:egeVjksOFHA.2468@.tk2msftngp13.phx.gbl...
> My trigger is on a VIEW, not on a table. Jack answered above though, and apparently explained why
> i get this behaviour.
> In EM, when you open a view, its not actually opening the view, but the underlying tables, only
> using the view definition. So triggers on the view DO get bypassed.
>