CODEWITHSUNDEEP
sqlsql-server
sz1
sz1

Reputation: 72

Insert trigger not inserting new rows in other table

Can anyone point out why this insert trigger is not inserting the new rows into the IVRTestB table?

  If Object_ID('MyTrig3', 'TR') IS Not Null
  Drop Trigger MyTrig3;
  GO

  Alter Trigger MyTrig3
  On dbo.IVRTest
  After Insert, update
    AS
  Begin
  SET NOCOUNT ON;
  Insert Into [dbo].[IVRTestB]  
  (IVRAID, IVRName, DayNumber, OpenFlag)  
  Select 
     'i.IVRAID', 'i.IVRName', 'i.DayNumber', 'i.OpenFlag'
          From inserted i
      INNER JOIN dbo.IVRTestB b
      On i.IVRAID = b.IVRAID
      END

Upvotes: 1

Views: 73

Answers (3)

sz1
sz1

Reputation: 72

Mohan,

You change nearly worked, I just had to change the not nulls to is not null and the alias for inserted to I

   Alter Trigger MyTrig3
  On dbo.IVRTest
  After Insert
    AS
  Begin
  SET NOCOUNT ON;
       IF EXISTS ( SELECT  
                      1  
                  FROM  
                      INSERTED  
                  WHERE  
                      INSERTED.DayNumber IS NOT NULL )  
  Insert Into 
  [dbo].[IVRTestB]  
  (IVRAID, 
  IVRName, 
  DayNumber, 
  OpenFlag)  
  Select 
     i.IVRAID,
      i.IVRName, 
      i.DayNumber, 
      i.OpenFlag
          From inserted i
       WHERE  
                 i.DayNumber IS NOT NULL  
      END

Upvotes: 0

marc_s
marc_s

Reputation: 754598

By putting every column of Inserted into single quotes, you're effectively inserting string literals into your destination table - not the column values!

Use this code instead:

INSERT INTO [dbo].[IVRTestB] (IVRAID, IVRName, DayNumber, OpenFlag)  
   SELECT
      i.IVRAID, i.IVRName, i.DayNumber, i.OpenFlag    -- *NO* single quotes here!!!!
   FROM   
      inserted i
   -- change this WHERE clause to insert those rows that AREN'T alredy in IVRTestB !
   WHERE
      i.IVRAID NOT IN (SELECT DISTINCT IVRAID FROM dbo.IVRTestB)

Upvotes: 2

mohan111
mohan111

Reputation: 8865

  Alter Trigger MyTrig3
  On dbo.IVRTest
  After Insert
    AS
  Begin
  SET NOCOUNT ON;
       IF EXISTS ( SELECT  
                      1  
                  FROM  
                      INSERTED  
                  WHERE  
                      INSERTED.DayNumber IS NULL )  
  Insert Into 
  [dbo].[IVRTestB]  
  (IVRAID, 
  IVRName, 
  DayNumber, 
  OpenFlag)  
  Select 
     i.IVRAID,
      i.IVRName, 
      i.DayNumber, 
      i.OpenFlag
          From inserted i
       WHERE  
                 INSERTED.DayNumber IS NULL  
      END

Upvotes: 0

Related Questions