sql server trigger question

Question

0.00/5 (No votes)

See more:

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

SQL

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)

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

I tried this

SQL

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

XML

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.

Posted 3-Apr-12 8:31am

postonoh

Updated 3-Apr-12 10:41am

v4

Add a Solution

Comments

[no name] 3-Apr-12 14:37pm

C# and T-SQL are two vastly different languages and technologies and you can't create a trigger in C# (other than executing a SQL statement to create it)
Which do you want?

ZurdoDev 3-Apr-12 15:08pm

You can actually do a managed code trigger, which is using C# to create a SQL trigger. However, there is too much wrong with the question. Just do a simple CASE statement or based on the example could probably get around the case statement and just parse the data.

postonoh 3-Apr-12 15:54pm

I know I can't create a trigger in c# i should have given a better description to my question. The problem with this is that I have three product that exist may all ready exist in the table what will stop it from loop through the table and insert multiple.

postonoh 3-Apr-12 16:16pm

What I am trying to accomplished is when a user saves a itemnumber it insert just the number item he has chosen.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shahin Khorshidnia · Accepted Answer · 2012-04-03T09:30:00

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.

Bala Selvanayagam · Accepted Answer · 2012-04-04T02:23:00

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

C#

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

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 trigger question

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0