I have set up a view with the Instead Of trigger to insert into multiple
underlying tables and it works great when the insert statement comes as
a SQL statement. As an example, it works when I run the insert from
query yzer or from an external application.


When I try to insert from Enterprise Manager, no luck. I get a 'Cannot
insert null values' error on the tables that get their values from the
primary table (The EntityID value below). It is like EM is trying to
evaluate the insert statement before running the Instead Of.

This is a problem for back end administration of the DB. Any thoughts?
(Trigger code listed below)




CREATE TRIGGER iv_Business ON [dbo].[v_Business]
Instead of INSERT
AS
Begin
if (select count(*) from inserted) >0
Begin
Declare EntityID int
Insert Into Entity
Select EntityStatusCd, EntityTypeCd
from Inserted
Set EntityID = Identity
Insert Into Business
Select EntityID, LegalName, FriendlyName
from Inserted

Insert Into Address
Select EntityID, AddressTypeCd, Address1, Address2, City,
StateCd, Zip
From Inserted

Insert Into Phone
Select EntityID, PhoneTypeCd, AreaCode, Phone, Extention
From Inserted
End
End

--
Posted via [url]http://dbforums.com[/url]