Help Required on Stored Procedure

Hello All,

I'm trying to write a stored procedure which tracks the insert event
in a Table A. When we insert something in Table A in Database 'FMDB',
it inserts the same information in Table B in Database 'Wartung'.

When I insert something with insert query visa SQL Query analyzer, it
works fine.

If i insert records via my C++ programme, it gives error that
ROLLBACK_TRANSACTION has no BEGIN TRANSACTION.

Trigger code is as follow:

CREATE TRIGGER [TG_TASKCOPY] ON [FMDB].[METABO_TASK]
FOR INSERT

AS

BEGIN TRANSACTION

IF (SELECT COUNT(*) FROM inserted)=1
BEGIN

DECLARE @.OBJEKT VARCHAR(32)
DECLARE @.TASK VARCHAR(32)
DECLARE @.PLANNER VARCHAR(32)
DECLARE @.RESPONSIBLE VARCHAR(32)
DECLARE @.STATUS VARCHAR(32)
DECLARE @.PLANDATE DATETIME
DECLARE @.ANLAGE_NUM int

SELECT @.OBJEKT=OBJECT FROM inserted
SELECT @.TASK=TASK FROM inserted
SELECT @.PLANDATE=PLANDATE FROM inserted

IF (SELECT COUNT(*) FROM WARTUNG.WARTUNG.IPS_ASSETS WHERE
FM_NUM=@.OBJEKT)=1
BEGIN
BEGIN TRANSACTION

SELECT @.ANLAGE_NUM=ANLAGE_NUM FROM WARTUNG.WARTUNG.IPS_ASSETS
WHERE FM_NUM=@.OBJEKT
INSERT INTO WARTUNG.WARTUNG.IPS_WARTUNG (ANLAGE,ANLAGE_NUM,
WAEHRUNG,NAECHSTEDATUM, STATUS,INTERVALTYPE,ART) VALUES(@.OBJEKT,
@.ANLAGE_NUM,'EUR', @.PLANDATE,'geplant','D',@.TASK)

COMMIT TRANSACTION
END

ELSE IF(@.@.error<>0)
BEGIN
ROLLBACK TRANSACTION
END

END

With Regards,
Attiq ur Rehman[posted and mailed, please reply in news]

Attiq ur Rehman (attiq76@.yahoo.com) writes:
> I'm trying to write a stored procedure which tracks the insert event
> in a Table A. When we insert something in Table A in Database 'FMDB',
> it inserts the same information in Table B in Database 'Wartung'.
> When I insert something with insert query visa SQL Query analyzer, it
> works fine.
> If i insert records via my C++ programme, it gives error that
> ROLLBACK_TRANSACTION has no BEGIN TRANSACTION.

It could have help if you had included the relevant parts of your
C++ code too.

Anyway, there are some problems with your trigger. You have an initial
BEGIN TRANSACTION which is not matched by a COMMIT or ROLLBACK. There
is not really any reason to issue any BEGIN TRANSACTION in a trigger,
because a trigger always runs in a transaction; defined by the statement
that fired the trigger. (Even if you don't have any user-defined
transaction, each INSERT, UPDATE or DELETE statement is its own transaction
in SQL Server).

There is also this funny line:

> ELSE IF(@.@.error<>0)
> BEGIN
> ROLLBACK TRANSACTION
> END

This is a meaningless piece of code. @.@.error is set after each
statement, and the previous statement is an IF statement, and yes,
an IF statement also sets @.@.error.

I suggest that you remove all BEGIN/COMMIT/ROLLBACK TRANSACTION
from the trigger.

Note also that when you rollback a transaction in a trigger this
cancels the execution of the entire batch. Likewise, if you get
an error in a trigger this also cancels the batch entire, and also
rolls back any transactions.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp