sql server触发问题

问题描述

我正在尝试使用if语句创建触发器.

或C#代码或LINQ

我创建一个触发器，每次购买产品时，它将ID和数量插入数量表.用于其他用途.

我想拥有的是

我的触发器是

在OrderDetails上创建触发器orderInsert

I am trying to create a trigger with if statements.

or c# code or LINQ

I create a trigger that every time product is purchased it insert the id and quantity into a quantity table. To be used for something else.

What I would like to have is

with my trigger is

creat trigger orderInsert on OrderDetails

if (select itemNumber from table where itemnumber = 'NUE-QAR-1')
insert select (1, id)
else if (select itemNumber from table where itemnumber = 'NUE-QAR-5')
insert select ( 5, id)
else if (select itemNumber from table where itemnumber = 'NUE-QAR-10')
insert select (10, id)

如果不能，是否可以使用C#或sql触发器提供代码段?

我试过了

is this possible if not can you give code snippet in C# or as a sql trigger.

I tried this

create trigger orderinsert on dbo.OrderDetails
for insert
AS
Begin

if(SELECT ItemNumber FROM dbo.OrderDetails) = 'NUE-QAR-1'
 insert into Quantity(Quantity, OrderDetailId)
 select 1, OrderDetailId
 from inserted
 else if(SELECT ItemNumber FROM dbo.OrderDetails) = 'NUE-QAR-5'
 insert into Quantity(Quantity, OrderDetailId)
 select 5, OrderDetailId
 from inserted
 else if(SELECT ItemNumber FROM dbo.OrderDetails) = 'NUE-QAR-10'
 insert into Quantity(Quantity, OrderDetailId)
 select 10, OrderDetailId
 from inserted
 end

错误消息

error message

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

推荐答案

CREATE TABLE [dbo].[OrderDetails](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[item_number] [varchar](50) NULL
) 

ON [PRIMARY]

ON [PRIMARY]

CREATE TABLE [dbo].[quantity](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[OrderDetailID] [int] NULL,
	[Quantity] [int] NULL
) ON [PRIMARY]

触发器如下

The trigger is as follows

Create trigger trUpdateQuantity
on OrderDetails for insert as
Begin
DECLARE @Code varchar(100)=(select Item_number from inserted)

insert into quantity(OrderDetailID,Quantity)
select inserted.id,1 from inserted
where @Code=''NUE-QAR-1''


insert into quantity(OrderDetailID,Quantity)
select inserted.id,5 from inserted
where @Code=''NUE-QAR-5''

结束

End

这篇关于sql server触发问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！