SQL Server AFTER INSERT trigger fail?
I want this trigger to be fired after an insert is made with the text married on the marital_status column, this is what I have so far
ALTER TRIGGER [dbo].[marriage] ON [dbo].[applicant_personal_info] AFTER INSERT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; IF (SELECT [marital_status] FROM inserted) = 'married' BEGIN INSERT INTO [dbo].[applicant_marriage_info]([dom]) VALUES('abc') END END Don’t know why SQL Server AFTER INSERT trigger fail? And what if after insert trigger fails?
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 ENDWhen 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.