CODEWITHSUNDEEP
sqlsql-server
Gaufrid Schäfer
Gaufrid Schäfer

Reputation: 1

SQL trigger question, insert trigger for multiple values at once

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.

Upvotes: 0

Views: 50

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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
                     );

Upvotes: 0

LOKIN1212
LOKIN1212

Reputation: 11

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

Upvotes: 1

Related Questions