Add a Row to a child table with INSTEAD OF INSERT TRIGGER

Jericho · **Joined:** Thu Aug 25, 2005 3:14 am **Posts:** 19

Hi All!

I found a problem with a simple Parent-Child example.
I have 2 classes
Parent and Child as described in documentation.
Then i add INSTEAD OF INSERT TRIGGER on table Cilds

Code:

CREATE TRIGGER dbo.ti_io_Child
INSTEAD OF INSERT
AS
BEGIN
insert into Child(Code)
select  Code+'_111'
from inserted

return
END

and after that I can't add new object in child collection and save it in Child table. From SQL Profiler i saw that TSQL Code that NHIbernate generate to save child object

Code:

exec sp_executesql N'INSERT INTO....;SELECT SCOPE_IDENTITY()'...

and when i run this query in QueryAnalyzer SELECT SCOPE_IDENTITY()' return NULL instead of Id of new added row, and therefore I catch exeption on client-side.

How workaround of this problem?? I can't remove trigger fron table - it very important for sequrity checking.
----
Thanks. And forgive me my terrible English.