Click here to Skip to main content
15,887,214 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
CREATE TRIGGER TRI_INSERTINSTOCK
ON instock
FOR INSERT
AS
BEGIN
DECLARE @storeid int

SELECT storeid FROM inserted


IF EXISTS(SELECT STOREID FROM instock WHERE storeid =@storeid)
IF NOT EXISTS(SELECT 0 FROM instock WHERE STOREID =@STOREID)
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
)
PRINT 'INSERT TRIGGER FIRED'
END
INSERT INTO instock(storeid,wineid,quantity)
VALUES(1,1003,10000)
SELECT storeid FROM instock
WHERE storeid =1
Posted
Comments
ZurdoDev 20-Jan-15 7:47am    
I don't quite follow the question.
Member 11387088 20-Jan-15 7:59am    
I've created two tables and i want to insert values into the second table when the condition matches.

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 -
SQL
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
 
Share this answer
 
v2
Hi,
Create separate Trigger for inserting the value in table. and
You can simple call the insert trigger when you are checking your condition.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900