SQL trigger question, insert trigger for multiple values at once

Question

I have a temporary table "table", a table called "sales", and a table called "company_address". Now I want to design a trigger in SQL SERVER for them.

"

Insert into table 
values ('CompA','USA','New York','Lee','555-777-8888'),
       ('CompA','USA','New York','Ron','555-777-8888'),
       ('CompB','Canada','Vancover','Yale','555-777-8888'),
       ('CompB','Canada','Vancover','Wendy','555-777-8888')

"

When I inserted the code above, the company name(1st column), the country name, the city name should be inserted into table "company_address". The sales' name, and the tel number should be inserted into table "persons".

By the way, if you can teach me how to use the NEWID function to generate a unique identifier for the company name, and how could the table "sales" inherited from it. That would be extremely helpful.

Triggers are not really intended to perform ETL. You may regret this design at some point. Case in point - the city is Vancouver. People misspell things all the time. Does the entire batch "fail" when a simple FK error occurs? And is there no actual relationship that needs to be established between the different parts of each row during insertion into the final tables? — SMor
– SMor, Commented Dec 23, 2020 at 13:19

LOKIN1212 · Accepted Answer · 2020-12-23 11:20:02Z

you cab check by the following:
as
begin
 DECLARE @Table TABLE
            (
             com varchar(50),
            cou varchar(50),
            city varchar(50),
            name varchar(50),
            phone varchar(50)
            );
             INSERT  INTO @Table
               (  com,cou,city,name,phone) select *   from inserted;
 
 declare @comid varchar(50);
  declare @perid varchar(50); 
  declare @com varchar(50);
 declare @cou varchar(50);
 declare @city varchar(50);

 declare @name varchar(50);
 declare @phone varchar(50);
 declare @companyTag int;
 declare @pertag int;
  declare cursor_a cursor for select com,cou,city  from @Table group by com,cou,city
  open cursor_a
  fetch next from cursor_a into @com,@cou,@city 
  while @@fetch_status=0
    begin
    select @companyTag=COUNT(*) from company where company=@com;
    if @companyTag=0 begin
    select @comid=NEWID();
     insert into company values( @comid,@com,@cou,@city); 
      end
      else begin
     select @comid=companyid from company where company=@com;
      end
declare cursor_b cursor for select t2.name,t2.phone 
     from company t1 ,@Table t2 
     where t1.com=t2.com and t1.companyid=@comid;
          open cursor_b
          fetch next from cursor_b into @name,@phone  
          while @@fetch_status=0
            begin
            select @pertag=COUNT(*) from Persons where name=@name;
            if @pertag=0 begin
            select @perid=NEWID(); 
             insert into persons values(@perid,@comid,@name,@phone);
             end
              fetch next from cursor_b into  @name,@phone  
            end
          close cursor_b
          deallocate cursor_b
 
      fetch next from cursor_a into @com,@cou,@city 
    end
  close cursor_a
  deallocate cursor_a 
end

Gordon Linoff · Accepted Answer · 2020-12-23 12:36:33Z

I would do this as separate inserts. First insert the company rows:

insert into company_address (name, country, city)
    select distinct name, country, city
    from "table" t
    where not exists (select 1
                      from company_address c
                      where c.name = t.name and c.country = t.country and c.city = t.country
                     );

Note that this checks that the company does not exist.

Then insert the persons. Presumably, there is also a company id in that table:

insert into persons (sales_name, phone, company_id)
    select distinct t.sales_name, t.phone, c.company_id
    from "table" t join
          company c
          on c.name = t.name and c.country = t.country and c.city = t.country
    where not exists (select 1
                      from persons p
                      where p.sales_name = t.sales_name and
                            p.company_id = c.company_id
                     );

Collectives™ on Stack Overflow

SQL trigger question, insert trigger for multiple values at once

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related