SQL Server AFTER INSERT trigger fail?

The issue you'll run into here comes from the fact that SQL Server doesn't have the "FOR EACH ROW" triggers that Oracle does. You have to write your triggers to handle multi-row changes, i.e. the INSERTED or DELETED virtual tables can have more than one row in them. If such an update were to happen, your trigger would fail, since (SELECT [marital_status] FROM inserted) would return multiple rows, and subqueries have to return a single value to be used in a direct comparison. The trigger would probably have to look something like this:

    CREATE TRIGGER [dbo].[marriage] ON [dbo].[applicant_personal_info] FOR INSERT AS BEGIN SET NOCOUNT ON IF EXISTS (SELECT [marital_status] FROM INSERTED WHERE marital_status = 'married') BEGIN INSERT INTO [dbo].[applicant_marriage_info] ([dom]) SELECT 'abc' --Replace with whatever you're really inserting FROM INSERTED WHERE marital_status = 'married' END END

When sql server trigger after insert fails: Your insert will either entirely succeed (insert into t1 and into t2 succeed) or neither will - both will "fail" because the statement itself failed. So, after your insert, if the trigger failed - the database will look like your insert never ever happened.