Error inside trigger

hi

there is a programing flow that I want to know exactly what will happen

1- A database which has 3 table T1,T2,T3

2-a Storeprocedure called STP

3- Inside stp we insert to T1 and T2

4-T2 has 2 triggers TRG1,TRG2

5 - TRG2 check some thing and raise error .

so, I want to know at this case all rows which inserted will rollback or no

You are inserting in more than one table, so if you want to rollback the whole transaction inside the trigger, you have to initiate an explicit transaction using "begin transaction" (before start inserting), if not, just the insert on T2 will be rolled back.

AMB

|||

TRG2 will ROLLBACK the INSERT into T2 ONLY.

As Alejandro indicated, if you wish to ROLLBACK ALL activites, you need to start a TRANSACTION inside STP.

|||

I did think like u said befor but !!! you try this :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[Cola] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Colb] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

go

exec sys.sp_addmessage @.msgnum= 50001, @.severity= 16, @.msgtext= N'User error', @.replace=N'replace'

go

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

createTRIGGER dbo.trg1

ON dbo.table_1

AFTER INSERT,UPDATE

AS

BEGIN

SETNOCOUNTON;

raiserror(50001,16,1)

END

GO

they make u a table (Table_1) and a trigger (Trg1)

as u can see in Trg1 an error will raise so if we insert any row it will be rollbacked. so try to insert :

INSERTINTO [dbo].[Table_1]

([Cola]

,[Colb])

VALUES

('aaa'

,'bbb')

then u can see a row added !!!

sql