我希望在条件满足时在另一个表中触发插入触发器 [英] I want insert trigger to be fired in another table when the condition satisfies
问题描述
创建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
推荐答案
如果我理解你的问题，你想发射一个触发器r在以下条件匹配时将行插入bigstores
1）在INSTOCK表中，商店的QUANTITY大于等于50000
2）商店已添加到商店表中
3）商店尚未在bigstores表中添加
然后这是脚本 -
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
这是我得到的结果 -
SQLResult
创建单独的触发器，用于在表中插入值。和
在检查条件时，您可以简单地调用插入触发器。
Hi,
Create separate Trigger for inserting the value in table. and
You can simple call the insert trigger when you are checking your condition.
这篇关于我希望在条件满足时在另一个表中触发插入触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
