CODEWITHSUNDEEP
sql-servert-sql
user30410
user30410

Reputation: 131

Insert Trigger Cannot capture value from INSERTED

   CREATE TRIGGER [dbo].[TUI_CRS] ON [dbo].[C_Rates_Shadow] 
   FOR UPDATE, INSERT
   AS BEGIN

    if exists (select 1 from INSERTED) begin

     RAISERROR('Result', 16, 1) 

    end

I cannot get error while inserting record. But record get inserted.

Upvotes: 1

Views: 51

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

Just because you produce an error message, that doesn't mean that the transaction will be automatically rolled back. There could be situations where you want to report an error condition and have a permanent effect on the database. So, if you want to prevent the insert activity and report an error, you have to do both of those things yourself.

So, for example, this script:

create table T (ID int not null)
go
create trigger T_T on T
after insert
as
    if exists(select * from inserted)
    begin
        RAISERROR('I''m an error but so what?',16,1)
    end
go
insert into T(ID) values (1),(2)
go
select * from T

Produces:

Msg 50000, Level 16, State 1, Procedure T_T, Line 6
I'm an error but so what?

ID
-----------
1
2

Whereas if we re-write the trigger:

delete from T
go
drop trigger T_T
go
create trigger T_T on T
after insert
as
    if exists(select * from inserted)
    begin
        RAISERROR('I''m an error and we''re going to stop',16,1)
        rollback
    end
go
insert into T(ID) values (1),(2)
go
select * from T

We get:

Msg 50000, Level 16, State 1, Procedure T_T, Line 6
I'm an error and we're going to stop
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.
ID
-----------

Upvotes: 3

Related Questions