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 analyzer 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