问题描述
创建TRIGGER TRI_INSERTINSTOCK
ON instock
FOR INSERT
AS
BEGIN
DECLARE @storeid int
SELECT storeid FROM inserted
如果EXISTS（从instock WHERE storeid = @ storeid中选择STOREID）
如果不是EXISTS（选择0来自instock WHERE STOREID = @ STOREID）
插入bigstores（storeid，city ，电话）
选择商店，城市，电话
来自商店
其中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
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
推荐答案
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
这是我得到的结果 -
这篇关于我希望在条件满足时在另一个表中触发插入触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！