SQL Server Performance Forum – Threads Archive

Trigger Problem with Insert/Update

I have a web form that inserts to a table. I have a db trigger whose job it is to check to see if each of several fields are filled in. If they are, the resulting input is a "Qualified Lead" status. If any of the leads are null or blank, it should result in a "Prospect" status. I have something wrong and can’t figure it out for the life of me!!! Any lead put in is automatically a "Qualified Lead" with this trigger in place. Where is my error? Thanks in advance!! CREATE TRIGGER [QualLead] ON [db].[table_name]
FOR INSERT, UPDATE, DELETE
AS
Update table_name
set status = ‘Qualified Lead’
where compid in
(select compid from inserted
where CompName is not null or CompName <> ‘" "’
and CompPhone is not null or CompPhone <> ‘" "’
and BillToCity is not null or BillToCity <>’" "’
and BillToState is not null or BillToState <>’" "’
and BillToZip is not null or BillToZip <>’" "’) Update table_name
set status = ‘Suspect Lead’
where compid in
(select compid from inserted
where CompName is null or CompName = ‘" "’
and CompPhone is null or CompPhone = ‘" "’
and BillToCity is null or BillToCity =’" "’
and BillToState is null or BillToState =’" "’
and BillToZip is null or BillToZip =’" "’)

---

CREATE TRIGGER [QualLead] ON [db].[table_name]
FOR INSERT, UPDATE, DELETE
AS
Update table_name
set status = ‘Qualified Lead’
where compid in
(select compid from inserted
where (CompName is not null or CompName <> ‘" "’)
and (CompPhone is not null or CompPhone <> ‘" "’)
and (BillToCity is not null or BillToCity <>’" "’)
and (BillToState is not null or BillToState <>’" "’)
and (BillToZip is not null or BillToZip <>’" "’)) Update table_name
set status = ‘Suspect Lead’
where compid in
(select compid from inserted
where (CompName is null or CompName = ‘" "’)
OR (CompPhone is null or CompPhone = ‘" "’)
OR (BillToCity is null or BillToCity =’" "’)
OR (BillToState is null or BillToState =’" "’)
OR (BillToZip is null or BillToZip =’" "’))
Hank

---

Thanks but now every lead is entered as qualified regardless of the fields that are empty

---

Hank, thanks. I have now narrowed the problem down to the way the form enters data when a field is not populated. I thought = ‘" "’ would work, but it doesn’t seem to. I’ve also tried ‘""’ without success…any thoughts?

---

Found the problem!! Thanks Hank!!

---

You’re welcome, glad you found it. Hank

---

]]>