Cadavre (2/27/2013)

---

Read up on XACT_ABORT. XACT_ABORT is by default set to OFF in most instances for code in SQL Server, but inside a trigger it is set to ON by default.

davidsatz (2/28/2013)

---

So I am not hearing anyone disagree with my conclusion that there is no way to accomplish this...

davidsatz (2/28/2013)

---

So I am not hearing anyone disagree with my conclusion that there is no way to accomplish this...

create table _test (id int, hundreddevidebyid int)
go
create table _test_log (logid int identity(1,1), error varchar(2000))
go
create trigger tr_test on _test for insert 
as
begin

    begin try
        update t
            set hundreddevidebyid = 100/i.id
        from _test t
        join inserted i on i.id = t.id
    end try
    begin catch
        declare @err varchar(2000) = error_message();
        rollback;
        insert _test_log select @err;
    end catch
    
end

insert _test (id) select 50;
insert _test (id) select 25;
insert _test (id) select 0;
select * from _test;
select * from _test_log; 

Eugene Elutin (2/28/2013)

---

I'm not exactly sure what you are looking to do, it would be helpful if you would provide some DDL to have a look (follow the link at the bottom to my signature - it explains what kind of details are expected from OP).
Anyway... If you are asking about possibility to log error which causes rallback from within a trigger, the answer is - it is possible. Here is small demonstration:

create table _test (id int, hundreddevidebyid int)
go
create table _test_log (logid int identity(1,1), error varchar(2000))
go
create trigger tr_test on _test for insert 
as
begin

    begin try
        update t
            set hundreddevidebyid = 100/i.id
        from _test t
        join inserted i on i.id = t.id
    end try
    begin catch
        declare @err varchar(2000) = error_message();
        rollback;
        insert _test_log select @err;
    end catch
    
end

insert _test (id) select 50;
insert _test (id) select 25;
insert _test (id) select 0;
select * from _test;
select * from _test_log; 

You will see, that the third INSERT fails as it will cause division by 0, but the error is logged into the table.
Now, if you amend the trigger and comment out the ROLLBACK , the logging will stop working.
So the rule is: if you want to log errors within a trigger then your trigger should have error-handling with explicit ROLLBACK...

...
He wants his original SQL statement to complete, but I'm not sure that's possible once you've encountered a constraint error. A relational transaction is, by definition, "all or none": so SQL can't just apply the "good" rows and ignore the "bad" ones.