0

I have a table where I can only have 2 rows with the state activated(1) so I have a trigger that would limit that.

Here's that trigger: 

ALTER TRIGGER [dbo].[ciclo_OI] 
ON [dbo].[ciclo] 
FOR INSERT 
AS 
BEGIN
    SET NOCOUNT ON

    IF (SELECT COUNT(*) FROM ciclo WHERE cicloEstado = 1) > 2
       ROLLBACK TRANSACTION
END
GO

The problem is that when I try to insert something in the table, I get this error:

Msg 334, Level 16, State 1, Line 1
The target table 'ciclo' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

What can I do to fix that?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ariel Penayo
  • 1
  • 3
    can you show the insert statement that gave the error? – Hogan Oct 22 '19 at 14:49
  • If you are going to rollback the transaction, would it not be nice to inform the process inserting those rows of the reason? You know - so it could at least catch (and possibly respond to) the error? – SMor Oct 22 '19 at 16:05
  • And the error message is very clear. Either don't use the output clause in the insert statement, use the into option with the output clause, or change your approach to catching the error. Demo and discussion by larnu [here](https://stackoverflow.com/questions/53004037/output-clause-vs-triggers) – SMor Oct 22 '19 at 16:09

2 Answers2

3

Don't use a trigger for this. You'll be better off with a filtered unique index:

CREATE UNIQUE INDEX UQ_one_cicloEstado
    ON dbo.ciclo_OI (cicloEstado)
    WHERE cicloEstado = 1;

Example:

CREATE TABLE dbo.TestTable (ID int IDENTITY,
                            SomeInt int,
                            SomeString varchar(10));

CREATE UNIQUE INDEX UQ_one_SomeInt
    ON dbo.TestTable (SomeInt)
    WHERE SomeInt = 1;
GO

INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(1,'asdkasd'); --Works. 
GO

INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(2,'asdfgdf'); --Works.

GO
INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(1,'sdfsdf'); --Fails.

GO

INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(2,'etrytrg'); --Works.
GO
SELECT *
FROM dbo.TestTable;

GO
DROP TABLE dbo.TestTable;

As the OP actually wants 2 rows, then the above isn't correct, but I have left it there.

A trigger still isn't the best place for this, in my view, but (unfortunately) that only leaves us with a multi-line scalar function. Far from ideal, and this could suffer from race conditions, but I suspect (due to the "Rule of 2") that it'll be unlikely. Here is an example:

CREATE TABLE dbo.TestTable (ID int IDENTITY,
                            SomeInt int,
                            SomeString varchar(10));



GO

CREATE FUNCTION dbo.CheckInt1Count()
RETURNS INT
AS BEGIN

    DECLARE @Count int = 0;
    SELECT @Count = COUNT(*)
    FROM dbo.TestTable
    WHERE SomeInt = 1;

    RETURN @Count;
END;
GO

ALTER TABLE dbo.TestTable ADD CONSTRAINT ck_int1Count CHECK (dbo.CheckInt1Count() <= 2);
GO

INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(1,'asdkasd'); --Works. 
GO

INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(2,'asdfgdf'); --Works.

GO
INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(1,'sdfsdf'); --Works.

GO

INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(2,'etrytrg'); --Works.
GO
INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(1,'jdgfhbsk'); --Fails.
GO
GO
INSERT INTO dbo.TestTable (SomeInt,
                           SomeString)
VALUES(2,'sdfipasdf'); --Works.

GO
SELECT *
FROM dbo.TestTable;
GO

DROP TABLE TestTable;
DROP FUNCTION dbo.CheckInt1Count;
Thom A
  • 88,727
  • 11
  • 45
  • 75
0

I would suggest putting the results of your count(*) into a variable and then querying the variable.

Theo
  • 31
  • 5