2

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
2
  • 2
    For one, your trigger doesn't handle the case INSERT dbo.TableName(source) VALUES('b'),(NULL);. A trigger fires once per DML action, not once per row. So assigning something from inserted to a variable is bad news. Commented Sep 9, 2014 at 16:07
  • Please don't cross-post. Commented Sep 9, 2014 at 16:25

2 Answers 2

1

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
1

I've tried to re-create what you're doing on my local instance, here's what I've got:-

I created two tables, one for the data to be inserted to and one for the trigger to insert to:-

CREATE TABLE dbo.[TriggerTest]
(Source         CHAR(1),
 TestColSource  CHAR(1));
GO

CREATE TABLE dbo.[InsertTest]
(TestColDest CHAR(1));
GO

I then created this trigger (as @Aaron Bertrand says, removing the variable assignment):-

CREATE TRIGGER TRG_TriggerTest
ON dbo.[TriggerTest]
FOR INSERT
AS 
BEGIN
    INSERT INTO dbo.[InsertTest]
        (TestColDest)  
    SELECT i.TestColSource
    FROM inserted i
    WHERE i.SOURCE <> 'b'
END

I then inserted some data into the initial table:-

INSERT INTO dbo.[TriggerTest]
SELECT 'a', '1'
UNION
SELECT 'b', '2'
UNION 
SELECT 'c', '3'

The trigger should only insert the 1 & 3 values from the above statement, but this can be checked by:-

SELECT TestColDest FROM dbo.[InsertTest]

Hopefully that should give you a basis for your trigger. I'm sure the guys on here will correct me if I've got something wrong.

3
  • @DBAFromTheCold .. Needs to check for NULL as WHERE i.SOURCE <> 'b' will not work if NULL is in Source
    – t_m
    Commented Sep 9, 2014 at 16:31
  • Thanks, adding AND i.source IS NOT NULL should do it Commented Sep 9, 2014 at 16:34
  • Thanks for the answer, i do not have enough points to vote up but you and t_m pointed in the right direction.
    – Hakan Zim
    Commented Sep 9, 2014 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.