I'm trying to find out if the "not like" operator is ignored in triggers.  I
have a fairly simple trigger, but it is firing for all inserts, disregarding
the
"IF @CustomerName not like '%solec%' RETURN"
statement.  I even set the name to null thinking it may have been holding
onto something.  I've researched as much as I can and don't find anything
that says I can't use it.

Thanks in advance for any input.


CREATE   TRIGGER InsertNewTaskSolec1002

ON dbo.TaskAssignment

  /*%ENCRYPT%*/ 

FOR INSERT

AS

/****************************************************************************************************************************
Name:        InsertNewTask
Description:    This trigger will fired off when a new taks is inserted into
the TaskAssignment table. It will
        result in inserting an email into the emailqueue table and send out an
email.
Created By:   
Created On:    March 27, 2001
Input Parameters:   
Inputs:   
Revision History:   
Sept.     11, 2002            Get the planned hours of the resource rather than of
the Task.
OCT.      29, 2002          Insert EmailQueueId as NEWId() in EmailQueue table
Feb       27, 2003            Check to see if the Resource has disabled Email
notification in his personal profile and if so exit the trigger.
July     16, 2003        Modified to handle batch inserts
Mar     17, 2004        Modified to handle larger string size for names
Feb     17, 2005            Change cast on plannedhours from 9 character to 12, it
was small and caused error
June    20, 2005        Modified select statement form ResourceEmail table 
*****************************************************************************************************************************/
DECLARE @EmailId uniqueIdentifier
DECLARE @CustomerName nvarchar(256)
DECLARE @ProjectName  nvarchar(256)
DECLARE @TaskName nvarchar(256)
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
DECLARE @PlannedHours Numeric(9,2)
DECLARE @Comment nvarchar(256)
DECLARE @EmailAddress nvarchar(50)
DECLARE @ResourceId uniqueIdentifier
DECLARE @CustomerId uniqueIdentifier
DECLARE @ProjectId uniqueIdentifier
DECLARE @TaskId uniqueIdentifier
DECLARE @ProjectStatus Varchar(3)
DECLARE @DisableEmail Bit
DECLARE @TResourceId uniqueIdentifier
DECLARE @CCEmail nvarchar(50)

SET @CustomerName = NULL
SET @ProjectName = NULL
SET @CCEMail='krobert***@tuvam.com'



IF (Select Count(*) from Inserted) =1
BEGIN

    IF EXISTS(SELECT TaskStatus FROM inserted WHERE TaskStatus is null)
    BEGIN
       SELECT @Emailid = EmailId FROM Email    -- EmailId is hardcoded in
consideration of Globalization
       WHERE EmailId= 'C5F1C618-BC63-4E87-8AAD-24974918FE13' AND Enabled = 1
AND Deleted = 0
       If @EmailId IS NULL RETURN

       SELECT @ResourceId = ResourceId, @CustomerId = CustomerId , @ProjectId =
ProjectId,
       @TaskId = TaskId, @PlannedHours = PlannedHours  
       FROM inserted

       SELECT @CustomerName = Name FROM Customer WHERE CustomerId = @CustomerId
       IF @CustomerName not like '%solec%' RETURN

       /*  If the Resource has disabled receiving the emails in his personnal
profile, exit the trigger. */
       SELECT @DisableEmail = DisableEmail FROM Resources WHERE ResourceId =
@ResourceId AND Deleted = 0
       IF @DisableEmail = 1 RETURN

       /* If the Resource has disabled this particular email, exit the trigger.
*/
       SELECT @TResourceId=ResourceId  FROM ResourceEmail WHERE EmailId =
@EmailId AND ResourceId = @ResourceId
       IF  @TResourceId IS NULL RETURN

       SELECT @EmailAddress = EmailAddress FROM ResourceAddress WHERE
ResourceId = @ResourceId
       IF @EmailAddress IS NULL RETURN

       SELECT @ProjectName = Name,@ProjectStatus=ProjectStatus FROM Project
WHERE ProjectId = @ProjectId
       IF @ProjectName IS NULL RETURN

       SELECT @TaskName = Name FROM Tasks WHERE TaskId = @TaskId
       IF @TaskName IS NULL RETURN

       SELECT @StartDate = PlannedStart FROM Tasks WHERE TaskId = @TaskId
       IF @StartDate IS NULL RETURN

       SELECT @EndDate = PlannedFinish FROM Tasks WHERE TaskId = @TaskId
       IF @EndDate IS NULL RETURN

       SELECT @Comment = ManagerComment
       FROM Taskassignment
       where
       ResourceId = @ResourceId and CustomerId = @CustomerId
       and ProjectId = @ProjectId and TaskId = @TaskId

       IF @ProjectStatus = 'A'
          Begin
             INSERT EmailQueue(EmailQueueId, EmailID, ToEmail, CCEmail,
BCEmail, SubjectParameters, BodyParameters)
                VALUES(NewID(),@EmailID, @EmailAddress, @CCEmail, NULL, NULL,
@CustomerName + '|' + @ProjectName + '|'
                + @TaskName+ '|' +  CAST(@StartDate AS CHAR(12)) + '|' +
                CAST(@EndDate AS CHAR(12)) + '|' +
LTRIM(RTRIM(CAST(@PlannedHours AS CHAR(12)))) + '|' + RTRIM(@Comment))
          END   
    END
END  -- If (select count(*) from Inserted) =1
ELSE
BEGIN

    SELECT     @Emailid = EmailId FROM Email    -- EmailId is hardcoded in
consideration of Globalization
    WHERE     EmailId= 'C5F1C618-BC63-4E87-8AAD-24974918FE13' AND Enabled = 1 AND
Deleted = 0

    If @EmailId IS NULL RETURN

    DECLARE  @TempAssignments table(    ResourceId   Uniqueidentifier ,
                                                        CustomerId  Uniqueidentifier ,
                        ProjectId  Uniqueidentifier ,
                                                        TaskId  Uniqueidentifier ,
                        EmailAddress varchar(100),
                        CustomerName nvarchar(255),
                        ProjectName nvarchar (255),
                        TaskName nvarchar(255),
                        StartDate datetime,
                        EndDate datetime,
                        PlannedHours numeric (9,2),
                        Comment Varchar(255))                         


    Insert Into @TempAssignments
    Select     I.ResourceId, I.CustomerId, I.ProjectId, I.TaskId, RA.EmailAddress,
C.Name, P.Name, T.Name,
        T.PlannedStart, T.PlannedFinish, I.PlannedHours, ''
    From     Inserted I
        Inner Join ResourceAddress RA on I.ResourceId = RA.ResourceId
        Inner Join Customer C on I.CustomerId = C.CustomerId
        Inner Join Project P on I.ProjectId = P.ProjectId And P.ProjectStatus = 'A'
        Inner Join Tasks T on I.TaskId = T.TaskId   

    Where    RA.EmailAddress is NOT NULL   
        And I.ResourceId Not IN (Select ResourceId from ResourceEmail E Where 
E.EmailId = @EmailId AND E.ResourceId = I.ResourceId)

    INSERT EmailQueue(    EmailQueueId,
                EmailID,
                ToEmail,
                CCEmail,
                BCEmail,
                SubjectParameters,
                BodyParameters)
                (SELECT NewID(),
                @EmailID,
                EmailAddress,
                @CCEmail,
                NULL,
                NULL,
                CustomerName + '|' + ProjectName + '|' + TaskName+ '|' +  CAST(StartDate
AS CHAR(12)) + '|' +
                        CAST(EndDate AS CHAR(12)) + '|' +
LTRIM(RTRIM(CAST(PlannedHours AS CHAR(12))))  + '|' + RTRIM(Comment)
                FROM @TempAssignments )


END

Just an observation (ignore if you're only interested in answers): I don't
really see any reason this trigger couldn't be made into one or two
INSERT...SELECT FROM INSERTED statements. Am I missing something?

ML

---
http://milambda.blogspot.com/

I'm really not that good at programming.  I'm trying to understand why that
doesn't work.

Thanks,

Show quoteHide quote

Well, please elaborate on "doesn't work". Are there errors? Do you get
unexpected or no results?

To give you an alternative, think about something fully set-based:

insert <destination table>
         (
         <column list>
         )
         select <column list>
                  from inserted
                         inner join <other tables>
                                        ...
                  where (<one set of conditions>)
         union
         select <column list>
                  from inserted
                         inner join <other tables>
                                        ...
                  where (<another set of conditions>)

That way your trigger also supports multiple inserts (as triggers should),
since triggers get fired by statement not by row.


ML

---
http://milambda.blogspot.com/

To add to ML's response, your logic will fail if NULL is involved in the
evaluation.  Perhaps that is the crux of the issue.

Instead of:
SELECT @CustomerName = Name FROM Customer WHERE CustomerId = @CustomerId
IF @CustomerName not like '%solec%' RETURN

Try:
SET @CustomerName = (SELECT Name FROM Customer WHERE CustomerId =
@CustomerId)
IF @CustomerName not like '%solec%'
    RETURN
ELSE
    SELECT @CustomerName as 'PROBLEM IN LOGIC'

It should also be noted that you have two branches of logic that are
dependant on the number of rows inserted by the triggering statement.  This
should be unnecessary - you only need the ELSE branch.  Just as importantly,
much of the special logic (such as your not like statement) are not included
within this branch.  Seems like an additional set of issues that you have
yet to address.

Note that you can use profiler to see the actual execution of statements
within the trigger - or put your own debugging statements in that should
immediately identify what value is used in your "not like" comparison.

One other thing to add.  If you are going to compare two strings it is best
to makes sure they are of the same type. You defined CustomerName as a
NVARCHAR so the string should be unicode as well.

IF @CustomerName not like N'%solec%' RETURN

Show quoteHide quote

reverse like?
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
inserting single precision data into sql server float column
SQL dilema
CTE vs. Table Variable (Paging)