title: sql server - trigger with cursor
body: <p>I am trying to design a landing table that will receive order lines and then a trigger will send the data to the appropriate tables (i.e. customer, order, order line).</p>
  
  <p>I was easily able to do this in MySQL with the new and old key words however I am having some issues in SQL Server.</p>
  
  <p>Firstly - is this the best approach for this type of problem?</p>
  
  <p>Secondly if it is I have tried to create a trigger which uses a cursor to iterate through the inserted table in SQL Server. unfortunately I keep getting an error </p>
  
  <blockquote>
    <p>Msg 16916, Level 16, State 1, Procedure _raw_b2c_lines_ins, Line 17 [Batch Start Line 6]<br>
    A cursor with the name 'ins_cusor' does not exist</p>
  </blockquote>
  
  <p>I have attached the code below (removed code for the order and order line table to simplify)</p>
  
  <pre><code>CREATE OR ALTER TRIGGER zap._raw_b2c_lines_ins
              on zap._b2c
              after insert
              as
              begin
  
              declare @v_phone_cleansed varchar
              ,   @phone varchar
              ,   @v_curr_cust_id varchar
              ,   @customer_key varchar
              ,   @t_order_id int
              ,   @customer_id varchar
              ,   @t_raw_id int
              --
              declare ins_cursor CURSOR FOR SELECT t_raw_id from inserted;
              --
  
              OPEN ins_cusor;
  
              FETCH NEXT from ins_cursor INTO @t_raw_id;
  
              WHILE (@@FETCH_STATUS = 0)
              --
              BEGIN
  
              set @v_phone_cleansed = dbo.CleanNumber((select billing_phone 
              from    inserted 
              where   t_raw_id = @t_raw_id
              ));
              set @v_curr_cust_id = (select  isnull(max(t_customer_id),0) +1 from sales.b2c_customer);
              --
              if exists(  select  1 
              from    sales.b2c_customer c
              join    inserted i
              on      lower(c.first_name) =   lower(i.billing_first_name)
              and     lower(c.last_name) =    lower(i.billing_last_name)
              and     lower(c.email) =        lower(i.billing_email)
              and     (   lower(c.country) != lower(i.billing_country)
              or  lower(c.city) !=    lower(i.billing_city)
              or  c.phone_cleansed != @v_phone_cleansed   
              )
              and     c.current_record = 1
              and     i.t_raw_id = @t_raw_id  ) 
              --
              Begin
              set @customer_key = (   select  top 1 customer_key
              from    sales.b2c_customer c
              join    inserted i
              on      lower(c.first_name) =   lower(i.billing_first_name)
              and     lower(c.last_name) =    lower(i.billing_last_name)
              and     lower(c.email) =        lower(i.billing_email)
              and     (   lower(c.country)!=  lower(i.billing_country)
              or  lower(c.city) !=    lower(i.billing_city)
              or  c.phone_cleansed != @v_phone_cleansed   
              )
              and     c.current_record = 1    
              and     i.t_raw_id = @t_raw_id
              );
              --
              update sales.b2c_customer 
              set current_record = 0 
              where   exists (select  *
              from    sales.b2c_customer c
              join    inserted i
              on      lower(c.first_name) =   lower(i.billing_first_name)
              and     lower(c.last_name) =    lower(i.billing_last_name)
              and     lower(c.email) =        lower(i.billing_email)
              and     (   lower(c.country) != lower(i.billing_country)
              or  lower(c.city) !=    lower(i.billing_city)
              or  c.phone_cleansed != @v_phone_cleansed
              )
              and     c.current_record = 1
              and     i.t_raw_id = @t_raw_id
              )
  
              insert into sales.b2c_customer(
              customer_key
              ,   last_name
              ,   first_name
              ,   email
              ,   country
              ,   city
              ,   phone
              ,   phone_cleansed
              ,   current_record
              ) select    @customer_key
              ,   dbo.InitCap(i.billing_last_name)
              ,   dbo.InitCap(i.billing_first_name)
              ,   i.billing_email
              ,   i.billing_country
              ,   dbo.InitCap(i.billing_city)
              ,   i.billing_phone
              ,   @v_phone_cleansed
              ,   1
              from    inserted i
              where   i.t_raw_id = @t_raw_id
              ;
              --
              end
              --
              if exists(  select  1 
              from    sales.b2c_customer c
              join    inserted i
              on      lower(c.first_name) =   lower(i.billing_first_name)
              and     lower(c.last_name) =    lower(i.billing_last_name)
              and     lower(c.email) !=       lower(i.billing_email)
              and     lower(c.country) =      lower(i.billing_country)
              and     lower(c.city) =         lower(i.billing_city)
              and     lower(c.phone_cleansed) =   @v_phone_cleansed
              and     current_record = 1
              and     i.t_raw_id = @t_raw_id
              ) 
              --
              Begin
              --
              set     @customer_key = (   select  top 1 customer_key
              from    sales.b2c_customer c
              join    inserted i 
              on      lower(c.first_name) =   lower(i.billing_first_name)
              and     lower(c.last_name) =    lower(i.billing_last_name)
              and     lower(c.email) !=       lower(i.billing_email)
              and     lower(c.country) =      lower(i.billing_country)
              and     lower(c.city) =         lower(i.billing_city)
              and     c.phone_cleansed =  @v_phone_cleansed
              and     i.t_raw_id = @t_raw_id
              );
              update sales.b2c_customer 
              set current_record = 0 
              where   exists (select  *
              from    sales.b2c_customer c
              join    inserted i
              on      lower(c.first_name) =   lower(i.billing_first_name)
              and     lower(c.last_name) =    lower(i.billing_last_name)
              and     lower(c.email) !=       lower(i.billing_email)
              and     lower(c.country) =  lower(i.billing_country)
              and     lower(c.city) =     lower(i.billing_city)
              and     c.phone_cleansed =  @v_phone_cleansed
              and     c.current_record = 1
              and     i.t_raw_id = @t_raw_id
              )
  
  
              insert into sales.b2c_customer(
              customer_key
              ,   last_name
              ,   first_name
              ,   email
              ,   country
              ,   city
              ,   phone
              ,   phone_cleansed
              ,   current_record
              )   select  @customer_key
              ,       dbo.InitCap(i.billing_last_name)
              ,       dbo.InitCap(i.billing_first_name)
              ,       i.billing_email
              ,       i.billing_country
              ,       dbo.InitCap(i.billing_city)
              ,       i.billing_phone
              ,       @v_phone_cleansed
              ,       1
              from    inserted i
              where   i.t_raw_id = @t_raw_id
              --
              end
              --                    
              if not exists (
              select  1 
              from    sales.b2c_customer c
              join    inserted i 
              on      lower(c.first_name) =   lower(i.billing_first_name)
              and     lower(c.last_name) =    lower(i.billing_last_name)
              and     lower(c.email) =        lower(i.billing_email)
              and     lower(c.country) =      lower(i.billing_country)
              and     lower(c.city) =         lower(i.billing_city)
              and     c.phone_cleansed =  @v_phone_cleansed
              and     c.current_record = 1
              and     i.t_raw_id = @t_raw_id
              ) 
              --
              Begin
              --
              insert into sales.b2c_customer( customer_key
              ,   last_name
              ,   first_name
              ,   email
              ,   country
              ,   city
              ,   phone
              ,   phone_cleansed
              ,   current_record
              )   select concat(lower(i.billing_first_name), lower(i.billing_last_name), @v_curr_cust_id)
              ,   dbo.InitCap(i.billing_last_name)
              ,   dbo.InitCap(i.billing_first_name)
              ,   i.billing_email
              ,   i.billing_country
              ,   dbo.InitCap(i.billing_city)
              ,   i.billing_phone
              ,   @v_phone_cleansed
              ,   1
              from inserted i 
              where i.t_raw_id = @t_raw_id;
              --
              end 
              --
  
              FETCH NEXT from ins_cursor INTO @t_raw_id;
  
              end
  
              close ins_cursor; 
              deallocate ins_cursor;
              end
  </code></pre>