Trigger to validate data

[BHScripter]

Hi:

I am trying to create a trigger that forces our end users to choose a "location" before the entire record can be saved. The location is stored in column suser1. The primary key for each record is hmy. Here is my attempt, it worked once and then never again.


CREATE TRIGGER Work_Order_Location_Reqd
ON MM2WO
FOR Insert, Update
AS
IF EXISTS (Select 1 from
Inserted i
inner join deleted d on d.hmy = i.hmy
where i.suser1 = '' or i.suser1 = Null)

BEGIN
RAISERROR ('Location Cannot Be Blank', 16, 10)
Rollback Tran
END

Thanks in advance!

 

[samshiell]

Firstly this isn't coded correctly. You should have "SELECT * FROM ...." rather than "SELECT 1 FROM ...". Yes your way will work but it is poor coding.

But the reason this isn't working is because you have i.suser1 = Null. This coding never returns what you expect as you cannot use = with a NULL value.

Your should code this as ..... i.suser1 IS NULL


Hope that helps.

Sam

 

[swampBoogie]

You should have "SELECT * FROM ...." rather than "SELECT 1 FROM ...". Yes your way will work but it is poor coding.

Why is that poor coding?