CODEWITHSUNDEEP
sql-serversql-server-2005
Parijat
Parijat

Reputation: 43

Stuck at creating a trigger

performing some MSSQL exercises, and I am trying to create a trigger. However, the solution I have, comes across as theoretically correct to me but it is not working.

The aim is to create a trigger for a table that has only two columns. One column is the primary key and is Identity and does not allow null values. The other column is one that ALLOWS NULL values. However, it permits NULL values ONLY FOR A SINGLE ROW in the entire table. Basically a trigger should fire for an insert/update operation on this table which attempts to insert/update the column to a NULL value when there is already an existing NULL value for the column in the table.

This condition I capture in my trigger code as follows:

After Insert, Update
AS 
set ANSI_WARNINGS OFF
If ( (select count(NoDupName) from TestUniqueNulls where NoDupName is null) > 1 )
BEGIN
Print 'There already is a row that contains a NULL value, transaction aborted';
ROLLBACK TRAN
END

However, the transaction executes itself nonetheless. I am not sure why this is happening and the trigger is not firing itself.

So anybody to enlighten my misgivings here?

I also have used set ANSI_WARNINGS OFF at the start of the trigger.

Upvotes: 2

Views: 439

Answers (2)

Piotr Rodak
Piotr Rodak

Reputation: 1941

Yeah that's a gotcha. The expression inside parens of the COUNT has to evaluate to not null, otherwise it will not be counted. So it is safer to use *, or 1 or any not nullable column in the expression. The most commonly encountered expression is '*', although you can come across '1' as well. There is no difference between these expressions in terms of performance. However if you use expression that can evaluate to null (like nullable column), your counts and other aggregations can be completely off.

create table nulltest(a int null)
go
insert nulltest(a) values (1), (null), (2)
go
select * from nulltest
select COUNT(*) from nulltest
select COUNT(1) from nulltest
select COUNT(a) from nulltest
go
drop table nulltest

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453298

count(col) only counts non null values so count(NoDupName) ... where NoDupName is null will always be zero. You would need to check count(*) instead.

I realise this is just a practice exercise but an indexed view might be a better mechanism for this.

CREATE VIEW dbo.NoMoreThanOneNull
WITH SCHEMABINDING
AS
SELECT NoDupName
FROM dbo.TestUniqueNulls
WHERE NoDupName IS NULL

GO

CREATE UNIQUE CLUSTERED INDEX ix ON dbo.NoMoreThanOneNull(NoDupName)

Upvotes: 2

Related Questions