sql server触发问题 [英] sql server trigger question

问题描述

我正在尝试使用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.

推荐答案

您好

在SQL Server中创建触发器的方法如下:创建触发器(Transact-SQL)

如果您想用C#创建一个真正的数据库触发器，我不这么认为！
因为触发发生在 databse 中的Befor/After之前或之后，而不是一个动作，所以我不知道有什么方法可以在C#中处理它！

但是:
您可以在C#中创建类似的内容(不是精确的触发器)，这取决于应用程序体系结构.
通常，您必须撰写委托和

Hello

To create trigger in SQL Server Look at this: CREATE TRIGGER (Transact-SQL)

If you want to create a real database trigger in C#, I don''t think so!
Because trigger occurs Befor/After than or instead of an action in databse, and I don''t know any way to handle it in C#!

But:
You are able create something like it (Not an exact triger) in C# and it depends on the application architecture.
Generally, you must compose delegate and Transaction to achieve the goal.
You know that you must use them in Insert/Update/Delete methods.

此处的关键是记住要写入表的触发器(OrderDetails)将具有新插入的触发器.表中的数据插入"，因此在触发器中可能必须使用插入"而不是"OrderDetails"

创建触发器的两种方法

1.使用SQL
2.使用"ryanb31"概述的C#-Clr托管方法

希望下面的小代码有助于使用SQL

The key point here is to remember that the trigger you are writting to the table (OrderDetails) will have the newly inserted data in a table called ''inserted'' so in your trigger you may have to use ''inserted'' instead of ''OrderDetails''

Two ways you can create triggers

1.Using SQL
2.Using C# - Clr managed method as outlined by "ryanb31"

Hope the little code below helps using SQL

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触发问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！