Posted by Erland Sommarskog on 05/03/07 22:24

(jay_wic@yahoo.com) writes:
> I do not know this is the correct way to do this, but somehow this
> isnt working. All I want is not to have a null value in field A if
> there is a value in field B
>
> heres the code
>
> CREATE TRIGGER tiu_name ON tblName
> FOR INSERT, UPDATE
> AS
> DECLARE @FieldA AS REAL, @FieldB AS REAL;
>
> SELECT @FieldA=FieldA, @FieldB=FieldB
> FROM Inserted;
>
> IF (@FieldB IS NOT NULL) AND (@FieldA IS NULL)
> RAISERROR('Error Message',1,2);
> GO

A common error with triggers: you assume that they fire once per row,
when they in fact fire once per statement. Thus, you cannot select into
variables, but you must work with the inserted table directly:

IF EXISTS (SELECT *
FROM inserted
WHERE fieldB IS NOT NULL and fieldA IS NULL)
BEGIN
ROLLBACK TRANSACTION
RAISERROR('Error message', 16, 1)
END

Note two other changes:

o Added ROLLBACK TRANSACTION to rollback back the statement that fired
the trigger.
o Increased the severity level from 1 to 16 in the RAISERROR statement.
Level 1-10 are informational only. Level 11 or higher raises an error.

Finally, there is a simpler solution, without a trigger, in this case.
Just add a table constraint:

CONSTRAINT ckt_nullcheck CHECK
(NOT (fieldB IS NOT NULL AND fieldA IS NULL))

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]