Database And Trigger

Suggest you try my other suggestion then which is to establish if there is a problem first (e.g. if the INSERT will cause a duplicate) and if so use RAISERROR or somesuch to signal the issue to the Client

The SAVEPOINT will work in a Stored Procedure, so if you call a Stored Procedure (e.g. instead of doing an INSERT directly from Dynamic SQL or similar) to make your INSERT that can have logic to handle the Rollback to Savepoint if incorrect data found.

1) I tried a stored procedure but the problem is i am not able to use "inserted" inside it as i guess it is outside the scope of the trigger.

2) Reg the raiseError i notice that adding a duplicate somehow seems to only cause an exception and not raise an error. Please look at the code below: INSERT INTO dbo.DTLOG( id,LogTime, ObjectName, LogReason, Msg) SELECT 1, getdate(), 'dtEvent_', @scopeid , CAST(@@IDENTITY AS NVARCHAR) FROM inserted i -- entering duplicate IF (@@ERROR > 0) BEGIN - do error handling

But the whole code is encapsulated within a try catch and it always goes to the CATCH part of the code. It can signal the issue but the main aim is whatever happens in the trigger it should not prevent the record from being written in the main table.

3) "SET XACT_ABORT OFF" is it a good option to use within a trigger. As it doesnot cause a rollback in case of an error in the main table.

thanks for your help.

The code being executed within the trigger is executed within the same transaction as the insert into the base table.

You'll need to put error catching code within the trigger in a TRY CATCH block so that if there is a problem the error isn't thrown. That or put logic in the trigger to ensure that no error would exist, and if an error would exist to ignore the records.

Something like

IF NOT EXISTS (SELECT * FROM Table2 WHERE ChildId IN (SELECT Id FROM inserted)            
BEGIN            
    INSERT INTO Table2            
    ...            
END            

Or something like this

INSERT INTO Table2            
SELECT ...            
FROM inserted            
WHERE NOT EXISTS (SELECT * FROM Table2 WHERE Table2.ChildId = inserted.Id)