471,066 Members | 1,300 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

constraint in Trigger

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

Please Help.

May 3 '07 #1
1 1330
(ja*****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 3 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

26 posts views Thread by Agoston Bejo | last post: by
8 posts views Thread by csomberg | last post: by
5 posts views Thread by Anthony Robinson | last post: by
2 posts views Thread by Kenneth Koenraadt | last post: by
7 posts views Thread by lenygold via DBMonster.com | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.