If I understand your question correctly, you want to fire a trigger to insert row into bigstores when below condition matches
1) Store has QUANTITY more than equal to 50000 in INSTOCK table
2) Store is already added in stores table
3) store is NOT already added in bigstores table
Then here is the script -
CREATE TRIGGER TRI_INSERTINSTOCK
ON instock
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @storeid int
SELECT @storeid=storeid FROM inserted
DECLARE @Is_instock bit
SET @Is_instock =0
SELECT @Is_instock = 1 FROM instock WHERE STOREID =@storeid
DECLARE @Is_bigStores bit
SET @Is_bigStores=0
SELECT @Is_bigStores= 1 FROM bigstores WHERE STOREID =@storeid
IF (@Is_instock=1 AND @Is_bigStores=0)
insert into bigstores(storeid,city,phone)
select storeid,city,phone
from stores
where storeid in(select storeid
from instock
group by storeid
HAVING sum(QUANTITY) >=50000 AND storeid=@storeid
)
END
and to test this script, I have created table structure like below. Read it so that easier for you to understand the script
--create tables
CREATE TABLE bigstores(storeid INT,city NVARCHAR(50),phone NVARCHAR(50))
CREATE TABLE stores (storeid INT,city NVARCHAR(50),phone NVARCHAR(50))
CREATE TABLE instock(storeid INT,QUANTITY INT)
--Insert date into instock
INSERT INTO instock
SELECT 1,10000
UNION ALL
SELECT 1,20000
UNION ALL
SELECT 2,40000
UNION ALL
SELECT 3,10000
UNION ALL
SELECT 3,40000
--Insert data to stores
INSERT INTO stores
SELECT 1,'CITY1','+111251252'
UNION ALL
SELECT 2,'CITY1','+111251285'
UNION ALL
SELECT 3,'CITY1','+111255455'
--Run query to creat trigger
--Run query to test trigger
INSERT INTO instock
SELECT 3,20000
SELECT * FROM bigstores
Here is result I get-
SQLResult