CODEWITHSUNDEEP
sql-server
Ariel Penayo
Ariel Penayo

Reputation: 1

SQL Server cant insert into table with trigger enabled

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?

Upvotes: 0

Views: 426

Answers (2)

Thom A
Thom A

Reputation: 95567

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;

Upvotes: 3

Theo
Theo

Reputation: 31

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

Upvotes: 0

Related Questions