Explicit Batch abort from trigger

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Explicit Batch abort from trigger

Author

Topic

REDDY
Starting Member

43 Posts

Posted - 2004-03-26 : 15:37:55

I have 2 triggers on a table ,Trg1,Trig2 (after update)

I set the order to fire Trg1 first

inside the Trg1 I want to abort the whole batch under certain conditions,I tried RAISERROR ,RETURN but the
execution is going further to Trig2 ,which I don't want
How to abort the whole batch explicitly from within Trg1??

Trg1 code is as below

----------------------------------------------------------------
CREATE TRIGGER [TRG_OrderItemLog] ON [dbo].[OrderItem]
FOR INSERT, UPDATE
AS
SET XACT_ABORT ON

IF Update(StatusCode)

BEGIN

IF EXISTS( SELECT * FROM deleted where statuscode = 200)
BEGIN
--Abort the Batch
--select * from [linkedserver1].db1.dbo.no_table ( this works)
END

--Rest of the code continues
---------------------------------------------------------------

if I use select of non exising tablename(no_table) on a linked server ,the batch is aborting fine,but I am not sure this is the good way to abort the batch

Any help is greately appriciated

Thanks
Reddy

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-27 : 23:59:42

BEGIN TRANSACTION TRAN_WRAP

BEGIN TRANSACTION CREATE_TABLE
IF (SELECT OBJECT_ID('djl_test_trigger_transactions')) IS NOT NULL
BEGIN
DROP TABLE djl_test_trigger_transactions
END

CREATE TABLE djl_test_trigger_transactions(
int_value INT PRIMARY KEY,
varchar_value VARCHAR(55))
COMMIT TRANSACTION CREATE_TABLE

BEGIN TRANSACTION TRIGGER1
DECLARE @sql NVARCHAR(4000)

SELECT @sql = '
CREATE TRIGGER [trg_djl_test_trigger_transactions_ins_1] ON [dbo].[djl_test_trigger_transactions]
FOR INSERT, UPDATE
AS

PRINT ''EXECUTING FIRST TRIGGER''

IF EXISTS(SELECT varchar_value FROM inserted WHERE varchar_value = ''' + 'aaa' + ''')
BEGIN

IF EXISTS( SELECT * FROM inserted WHERE int_value = 2)
BEGIN
RAISERROR(''' + 'Invalid entry.' + ''',16,1)
ROLLBACK WORK
RETURN
END
END

IF EXISTS(SELECT int_value FROM inserted WHERE int_value = 1)
BEGIN

UPDATE djl_test_trigger_transactions SET varchar_value = ''' + 'aab' + ''' WHERE int_value = 1

END '

EXEC(@sql)

COMMIT TRANSACTION TRIGGER1

BEGIN TRANSACTION TRIGGER2

SELECT @sql = '
CREATE TRIGGER [trg_djl_test_trigger_transactions_ins_2] ON [dbo].[djl_test_trigger_transactions]
FOR INSERT, UPDATE
AS
BEGIN

PRINT ''EXECUTING SECOND TRIGGER''

IF EXISTS(SELECT * FROM inserted WHERE varchar_value = ''' + 'aab' + ''')
BEGIN

UPDATE djl_test_trigger_transactions SET int_value = int_value + 12 WHERE varchar_value = ''' + 'aab' + '''

END
ELSE
BEGIN

INSERT djl_test_trigger_transactions(int_value, varchar_value)
SELECT MAX(int_value)+2,''axs'' FROM djl_test_trigger_transactions

END

END'

EXEC(@sql)

COMMIT TRANSACTION TRIGGER2

BEGIN TRANSACTION TRIGGER_ORDER

EXEC sp_settriggerorder 'trg_djl_test_trigger_transactions_ins_1', 'first', 'UPDATE'

COMMIT TRANSACTION TRIGGER_ORDER

COMMIT TRANSACTION TRAN_WRAP
GO

SELECT * FROM djl_test_trigger_transactions

INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(1,'zzz')

SELECT * FROM djl_test_trigger_transactions

INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(2,'aaa')

SELECT * FROM djl_test_trigger_transactions

INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(4,'aaa')

SELECT * FROM djl_test_trigger_transactions

INSERT djl_test_trigger_transactions(int_value, varchar_value) VALUES(2,'aab')

SELECT * FROM djl_test_trigger_transactions

Run sp_configure and see what the settings are. You should be able to run the TRAN_WRAP above, then run each of the inserts above and get the following results:

INSERT 1:

EXECUTING SECOND TRIGGER
EXECUTING FIRST TRIGGER

(1 row(s) affected)

(1 row(s) affected)

int_value varchar_value
----------- -------------------------------------------------------
13 aab
15 axs

(2 row(s) affected)

INSERT 2:

EXECUTING FIRST TRIGGER
Server: Msg 50000, Level 16, State 1, Procedure trg_djl_test_trigger_transactions_ins_1, Line 13
Invalid entry.

--Notice, it "did not" run the second trigger.

INSERT 3:

EXECUTING FIRST TRIGGER
EXECUTING SECOND TRIGGER
EXECUTING FIRST TRIGGER

(1 row(s) affected)

(1 row(s) affected)

int_value varchar_value
----------- -------------------------------------------------------
4 aaa
13 aab
15 axs
17 axs

(4 row(s) affected)

INSERT 4:

EXECUTING FIRST TRIGGER
EXECUTING SECOND TRIGGER
EXECUTING FIRST TRIGGER

(2 row(s) affected)

(1 row(s) affected)

int_value varchar_value
----------- -------------------------------------------------------
4 aaa
14 aab
15 axs
17 axs
25 aab

(5 row(s) affected)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources