SQL Server – Insert Trigger Checking for Specific Column

sql server

I have got an insert trigger on a table. The table got a field named 'source'.
The source field could be populated with values b or left empty as in null.
i want the trigger only to fire if the source field is not populated with 'b'.
The below trigger codes deosnt give me error, but never runs.
if i remove the if condition it runs, but i need to check for value of 'b' before it should run.

Create TRIGGER TriggerName
ON tableName
FOR INSERT
AS 
BEGIN

DECLARE @source nvarchar(50)

SELECT                  @source = (select source  from inserted)

             if not (@source = 'b') 
                begin

                INSERT INTO ATable
                (field1,field2,field3)  
                SELECT i.field1,i.field2,i.field3
                FROM inserted i 

                    end
end

Best Answer

As per Aaron Bertrand you should do this ..

Create TRIGGER trigger_testtable
ON dbo.test
FOR INSERT
AS 
BEGIN

            INSERT INTO ATable
            (field1,field2,field3)  
            SELECT i.field1,i.field2,i.field3
            FROM inserted i 
                where (i.[source] IS NULL OR i.[source] <> 'b')

END
Related Question