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
