SQL Server：ROLLBACK TRANSACTION请求没有相应的BEGIN TRANSACTION

I have a trigger that works (it fires when it has to) but I still get an error. I understand the error but I don't know how to resolve it.

我有一个可以工作的触发器(它必须触发)但我仍然收到错误。我理解错误,但我不知道如何解决它。

I tried to put some BEGIN TRANSACTION with all the code who go with it but I think my grammar is wrong because I always get a timeout!

我尝试将一些BEGIN TRANSACTION与所有使用它的代码放在一起,但我认为我的语法错了,因为我总是会超时!

So my question is, where exactly do I have to put my BEGIN TRANSACTION statements in my code?

所以我的问题是,我在哪里必须将我的BEGIN TRANSACTION语句放在我的代码中?

Also, do I need 3 BEGIN TRANSACTION statements since I have 3 ROLLBACK?

另外,我需要3个BEGIN TRANSACTION语句,因为我有3个ROLLBACK吗?

Thank you in advance!

先谢谢你!

My code:

ALTER TRIGGER [dbo].[Tr_CheckOverlap]
ON [dbo].[Tranche]
FOR INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @IdVol INT, @IdTranche INT, 
            @AgeMinInserted DATE, @AgeMaxInserted DATE

    SELECT @AgeMinInserted = t.TRA_Age_Min 
    FROM Tranche t
    JOIN inserted AS i ON t.TRA_Id = i.TRA_Id

    SELECT @AgeMaxInserted = t.TRA_Age_Max 
    FROM Tranche t
    JOIN inserted AS i ON t.TRA_Id = i.TRA_Id

    DECLARE CR_TrancheVol CURSOR FOR 
        SELECT t.TRA_Vol_Id,t.TRA_Id
        FROM Tranche t
        JOIN inserted AS i ON t.TRA_Vol_Id = i.TRA_Vol_Id;

    OPEN CR_TrancheVol

    FETCH CR_TrancheVol INTO @IdVol, @IdTranche

    WHILE( @@FETCH_STATUS = 0)
    BEGIN
        DECLARE @AgeMin DATE, @AgeMax DATE

        SELECT @AgeMin = t.TRA_Age_Min 
        FROM Tranche t
        WHERE t.TRA_Id = @IdTranche

        SELECT @AgeMax = t.TRA_Age_Max 
        FROM Tranche t
        WHERE t.TRA_Id = @IdTranche

        IF @AgeMinInserted > @AgeMin AND @AgeMinInserted < @AgeMax
        BEGIN
            PRINT 'Trans1'
            RAISERROR('Overlap: Date de naissance minimum déjà couverte', 1, 420)
            ROLLBACK TRANSACTION
        END

        IF @AgeMaxInserted > @AgeMin AND @AgeMaxInserted < @AgeMax
        BEGIN
            PRINT 'Trans2'
            RAISERROR('Overlap: Date de naissance maximum déjà couverte', 1, 421)
            ROLLBACK TRANSACTION
        END

        IF @AgeMinInserted < @AgeMin AND @AgeMaxInserted > @AgeMax
        BEGIN
            PRINT 'Trans3'
            RAISERROR('Overlap: Tranche déjà couverte complètement', 1, 422)
            ROLLBACK TRANSACTION
        END

        FETCH CR_TrancheVol INTO @IdVol, @IdTranche
    END

    CLOSE CR_TrancheVol
    DEALLOCATE CR_TrancheVol
END

EDIT:

Okay, so I tried your answer without cursor (I understand that my way was clearly not the best!) but for now it doesn't work.

好的,所以我尝试了没有光标的答案(我明白我的方式显然不是最好的!)但是现在它不起作用。

My goal: I have a DB to book a flight. In this DB, i have a table "Tranche" who contains some dates and some prices (depending when the flight is).

我的目标:我有一个DB来预订航班。在这个数据库中,我有一个“Tranche”表,其中包含一些日期和一些价格(取决于航班时间)。

I need to prevent and avoid any overlap of birthdate, for example:

我需要预防和避免生日的任何重叠,例如:

1y-17y: 80€
18y-64y: 120€

So my trigger has to fire when I try to insert 17y-63y: xx € (because I already have a price for those ages).

因此,当我尝试插入17y-63y时,我的触发器必须触发:xx€(因为我已经为这些年龄段定价)。

Sorry if my English is not perfect btw!

对不起,如果我的英语不完美btw!

Here's my table "Tranche":

这是我的表“Tranche”:

https://i.stack.imgur.com/KuQH8.png

TRA_Vol_ID is a foreign key of another table "Vol" who contain the flights

TRA_Vol_ID是包含航班的另一个表“Vol”的外键

Here's the code I have atm:

这是我的代码:

ALTER TRIGGER [dbo].[Tr_CheckOverlap]
ON [dbo].[Tranche]
FOR INSERT
AS
BEGIN
    /*
    Some SQL goes here to get the value of Minimum age.
    I assuming that it doesn't vary by entry, however,
    I don't really have enough information to go on to tell
    */
    SET NOCOUNT ON;

    DECLARE @MinAge DATE, @MaxAge DATE

    SELECT @MinAge = t.TRA_Age_Min 
    FROM Tranche t
    JOIN Vol AS v ON v.VOL_Id = t.TRA_Vol_Id
    JOIN inserted AS i ON t.TRA_Id = i.TRA_Id
    WHERE t.TRA_Id = i.TRA_Id

    SELECT @MaxAge = t.TRA_Age_Max
    FROM Tranche t
    JOIN inserted AS i ON t.TRA_Id = i.TRA_Id
    JOIN Vol AS v ON v.VOL_Id = t.TRA_Vol_Id
    WHERE t.TRA_Id = i.TRA_Id

    IF (SELECT COUNT(CASE WHEN i.TRA_Age_Min > @MinAge AND i.TRA_Age_Min < @MaxAge  THEN 1 END) FROM inserted i) > 0 
    BEGIN
        RAISERROR('Overlap: Birthday min reached',1,430);
        ROLLBACK
    END
    ELSE IF (SELECT COUNT(CASE WHEN i.TRA_Age_Max > @MinAge AND i.TRA_Age_Max < @MaxAge  THEN 1 END) FROM inserted i) > 0 
    BEGIN
        RAISERROR('Overlap: Birthday max reached',1,430);
        ROLLBACK
    END
END

1 个解决方案

#1

I don't really know what the OP's goals are here. However, I wanted to post a small example how to do a dataset approach, and how to check all the rows in one go.

我真的不知道OP的目标是什么。但是,我想发布一个小例子如何进行数据集方法,以及如何一次性检查所有行。

At the moment, the trigger the OP has will only "work" if the user is inserting 1 row. Any more, and things aren't going to work properly. Then we also have the problem of the CURSOR. I note that the declaration of the cursors aren't referencing inserted at all, so I don't actually know what their goals are. It seems more like the OP is auditing the data already in the table when a INSERT occurs, not the data that is being inserted. This seems very odd.

目前,如果用户插入1行,OP的触发器将仅“工作”。更多,事情不会正常工作。然后我们也遇到了CURSOR的问题。我注意到游标的声明根本没有引用插入,所以我实际上并不知道它们的目标是什么。看起来更像OP正在审核INSERT发生时已经存在于表中的数据,而不是正在插入的数据。这看起来很奇怪。

Anyway, this isn't a solution for the OP, however, I don't have enough room in a comment to put all this. Maybe it'll push the OP in the right direction.

无论如何,这不是OP的解决方案,但是,我没有足够的空间来评论所有这些。也许它会把OP推向正确的方向。

ALTER TRIGGER [dbo].[Tr_CheckOverlap]
ON [dbo].[Tranche]
FOR INSERT
AS
BEGIN

    /*
    Some SQL goes here to get the value of Minimum age.
    I assuming that it doesn't vary by entry, however,
    I don't really have enough information to go on to tell
    */

    IF (SELECT COUNT(CASE WHEN i.Age < @MinAge THEN 1 END) FROM inserted i) > 0 BEGIN
        RAISERROR('Age too low',1,430);
        ROLLBACK
    END
    ELSE
    IF (SELECT COUNT(CASE WHEN i.Age > @MaxAge THEN 1 END) FROM inserted i) > 0 BEGIN
        RAISERROR('Age too high',1,430);
        ROLLBACK
    END


END

The question at hand seems to very much be an xy question; the problem isn't the CURSOR or the ROLLBACK, the problems with this trigger are much more fundamental. I'd suggest revising your question and actually explaining your goal of what you want to do with your Trigger. Provide DDL to CREATE your table and INSERT statements for any sample data. You might want to also provide some INSERT statements that will have different results for your trigger (make sure to include ones that have more than one row to be inserted at a time).

手头的问题似乎是一个xy问题;问题不在于CURSOR或ROLLBACK,这个触发器的问题更为根本。我建议修改你的问题并实际解释你想要用你的触发器做什么的目标。提供DDL以创建任何样本数据的表和INSERT语句。您可能还想提供一些INSERT语句,这些语句将为您的触发器提供不同的结果(确保包含一次插入多行的那些语句)。

I realise this is more commenting, however, again, there is definitely not enough room in a comment for me to write all this. :)

我意识到这是更多的评论,然而,再一次,我没有足够的空间评论我写这一切。 :)