SQL触发器update和insert

Question

我有三张桌子OUTPUT，INVENTORY和PRODUCT

Create Table OUTPUT(){
       OUTPUTID int indentity, -- output ID
       PRODUCTID varchar(10),
       QUANTUM int,
}
Create Table INVENTORY(){
       INVENTORYID int indentity, -- inventory ID
       QUANTUM int,
       QUANTUMOUT int, -- quantum out
}
Create Table PRODUCT(){
       PRODUCTID varchar(10),
}       

我已经创建了以下触发器：

create trigger [dbo].[trig_OUTPUT]
on [dbo].[OUTPUT]
after insert,update
as
begin
 Set nocount on;

    UPDATE INVENTORY 
    set INVENTORY.QUANTUMOUT = OUT.QUANTUM 
    from OUTPUT 
    inner join PRODUCT    on OUTPUT.PRODUCTID = PRODUCT.PRODUCTID 
    inner join INVENTORY  on PRODUCT.PRODUCT = INVENTORY.PRODUCT

end

和存储过程

CREATE procedure [dbo].[spud_addOUTPUT]
@productID varchar(10),
@quantum int
AS

 declare @quantumtemp int

  set @quantumtemp = (select QUANTUM from INVENTORY where INVENTORY.PRODUCTID = @productID)

if(@quantumtemp> @quantum or ISNULL(@quantumtemp,'0')='0')
 begin
    INSERT OUTPUT(PRODUCTID,QUANTUM) values(@productID,@quantum)
 end
else
  begin
    print 'do not insert quantum out greater quantum in inventory'
  end

然而，存储过程只适用于第一个，然后我调用一个存储过程。它没有更新INVENTORY中的列QUANTUMOUT

Answer 1

我不知道你在这里使用的是什么逻辑，但我已经修复了语法错误，

切勿使用sql server关键字作为对象名称，如果确实有任何对象命名为任何关键字，请始终使用方括号[]将它们括起来。

触发器

create trigger [dbo].[trig_OUTPUT]
on [dbo].[OUTPUT]
after insert,update
as
begin
 Set nocount on;

    UPDATE INVENTORY 
    set INVENTORY.QUANTUMOUT = [OUTPUT].QUANTUM   --<-- this should be OUTPUT
    from [OUTPUT] 
    inner join PRODUCT    on [OUTPUT].PRODUCTID = PRODUCT.PRODUCTID 
    inner join INVENTORY  on PRODUCT.PRODUCT = INVENTORY.PRODUCT

end

Procedure

CREATE procedure [dbo].[spud_addOUTPUT]
@productID varchar(10),
@quantum int
AS
BEGIN
  SET NOCOUNT ON;
 declare @quantumtemp int

SELECT TOP 1 @quantumtemp = QUANTUM from INVENTORY where PRODUCTID = @productID

if(@quantumtemp> @quantum or ISNULL(@quantumtemp,'0')='0')
 begin
    INSERT INTO dbo.[OUTPUT](PRODUCTID,QUANTUM) 
    values(@productID,@quantum)
 end
else
  begin
    print 'do not insert quantum out greater quantum in inventory'
  end
END