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
if (select count(*) from inserted) >0
Declare @EntityID int
Insert Into Entity
Select EntityStatusCd, EntityTypeCd
Set @EntityID = @@Identity
Insert Into Business
Select @EntityID, LegalName, FriendlyName
Insert Into Address
Select @EntityID, AddressTypeCd, Address1, Address2, City, StateCd, Zip
Insert Into Phone
Select @EntityID, PhoneTypeCd, AreaCode, Phone, Extention