Check Data Before Update

[Martin]

Hello Everybody!

I have a POLINE table on a SQL Server 2000 DB. Before I update the
record I need to check that either field, STORELOC or WONUM has data
on it. If both fields are NULL I would like to send a message letting
the user know that either fields needs data before they can save the
record. If any of the fields have data then, it is OK to save the
record.

Could you please let me know how to accomplish this? An example will
be really helpful, I can do this in Access but I do not know how to do
it in SQLServer. I was thinking using trigger but there are not really
good examples.

Thanks in Advance!

Martin

[Erland Sommarskog]

Re: Check Data Before Update

[posted and mailed, please reply in news]

Martin (martin.wunder@ wsidc.com) writes:[color=blue]
> I have a POLINE table on a SQL Server 2000 DB. Before I update the
> record I need to check that either field, STORELOC or WONUM has data
> on it. If both fields are NULL I would like to send a message letting
> the user know that either fields needs data before they can save the
> record. If any of the fields have data then, it is OK to save the
> record.
>
> Could you please let me know how to accomplish this? An example will
> be really helpful, I can do this in Access but I do not know how to do
> it in SQLServer. I was thinking using trigger but there are not really
> good examples.[/color]

The answer is that you don't do this in SQL Server.

No, get me right. You can certainly have a check for this in SQL Server:

create table POLINE (...
STORELOC some_type not null,
WONUM other_type not null,
...
constraint POLINE_CHK_STOR ELOC_WONUM
check (STORELOC is not null or WONUM is not
null)

This table constraint will cause SQL Server to flatly accept any rows
where both columns are NULL. If exactly one should be non-NULL the
check should read:

STORELOCK is not null and WONUM is null or
STORELOCK is null and WONUM is not null

However, this does not really meet your requirements, because the
message will not be useful to the user. To this end, you need to
do the check in the client.

There is an important difference between Access and SQL Server. Access
is both database and GUI in one, but SQL Server is only the server
side, and requires a client on the other end. (Which could be Access.)

Still constraints like these are very useful, because they can cover
up for tests that the GUI programmer failed to include, and protect
the integirty of the data.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp