Robert Burns
asked on
Another Trigger head scratcher
Been working on this for a couple days.
Right now my trigger looks like this:
CREATE TRIGGER FM_Insert_Location
ON spemp
FOR INSERT, UPDATE
AS
INSERT FM_Location
select i.email_address, i.spbldinf_code, i.spfloors_code, i.spbldrom_code,
'N', GetDate()
from inserted i join deleted d on i.spemp_code = d.spemp_code
where i.spbldrom_code <> d.spbldrom_code
Everything works fine, but I would like it to recognize when the email_address field has a null or blank value and NOT post the record in those cases.
I feel like I've tried everything :) Thoughts?
Right now my trigger looks like this:
CREATE TRIGGER FM_Insert_Location
ON spemp
FOR INSERT, UPDATE
AS
INSERT FM_Location
select i.email_address, i.spbldinf_code, i.spfloors_code, i.spbldrom_code,
'N', GetDate()
from inserted i join deleted d on i.spemp_code = d.spemp_code
where i.spbldrom_code <> d.spbldrom_code
Everything works fine, but I would like it to recognize when the email_address field has a null or blank value and NOT post the record in those cases.
I feel like I've tried everything :) Thoughts?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
CREATE TRIGGER FM_Insert_Location
ON spemp
FOR INSERT, UPDATE
AS
INSERT FM_Location
-- (good practice to put the list of column names here, makes maintenance MUCH simpler)
select i.email_address, i.spbldinf_code, i.spfloors_code, i.spbldrom_code,
'N', GetDate()
from inserted i
left outer join deleted d
on i.spemp_code = d.spemp_code
where i.spbldrom_code <> d.spbldrom_code
and i.email_address is not null
ASKER
Oi vey! the number of ways I tried this and it was so simple.
ASKER
But I have to admit, the ending syntax of this line leaves me wondering:
and IsNull(i.email_address, '') <> ''
The first quote is inside the parenthesis and the other isn't?
and IsNull(i.email_address, '') <> ''
The first quote is inside the parenthesis and the other isn't?
It is not double quote. These are 2 single quotes.
IsNull(i.email_address, '') returns '' when the email_address is null otherwise it returns actual email_address. Now we compare it with empty string and that is the condition.
IsNull(i.email_address, '') returns '' when the email_address is null otherwise it returns actual email_address. Now we compare it with empty string and that is the condition.
ASKER
Ahhh, I see. Thanks a bunch for the explanation.
CREATE TRIGGER FM_Insert_Location
ON spemp
FOR INSERT, UPDATE
AS
INSERT FM_Location
select i.email_address, i.spbldinf_code, i.spfloors_code, i.spbldrom_code,
'N', GetDate()
from inserted i join deleted d on i.spemp_code = d.spemp_code
where i.spbldrom_code <> d.spbldrom_code
And IsNull(email_address, '') <> ''