Re: Trigger Problem - Database Discussion Boards

Re: stored procedure error

Saira Tanwir7-Nov-06 0:05

Saira Tanwir

7-Nov-06 0:05

hi uroojkhan
u were a big help. it just solved my problem.

thanks Rose | [Rose]

and regards
Saira

Re: stored procedure error

Mairaaj Khan7-Nov-06 0:19

Mairaaj Khan

7-Nov-06 0:19

check this[^]

________________________________
Success is not something to wait for, its something to work for.

Trigger Problem

monika_vasvani6-Nov-06 20:44

monika_vasvani

6-Nov-06 20:44

Hi Every1

I have problem in Trigger.I am new for that.Pls help me.

I have one table as Try.When I insert values in Try,at a time I want to fire Trigger for inserting and insert that value also in another table as Try1.

I got help but not understand why create temporary table in Trigger.Can we use direct table name in Trigger?

Pls help me if u have solution for triggers.Becoz I am completely new in this concept.

Thanks
monika

Re: Trigger Problem

Coding C#7-Nov-06 0:24

Coding C#

7-Nov-06 0:24

hi,

Can u tell exactly what problem ur facing?
giving code snippet ll be better.

Nitin...

Re: Trigger Problem

vbkenya7-Nov-06 5:00

vbkenya

7-Nov-06 5:00

You don't have to create a temporary table in the trigger to insert values into another table. All the inserted values are available to you in a SQL Server defined table called 'inserted'. All you have to do is to read the values from this table and insert them into Try1.

For example, for single row insert operations:

CREATE TRIGGER Try_Insert
ON Try
FOR INSERT
AS
INSERT INTO Try1 (columnsList)
SELECT ColumnsList FROM Inserted

Nathan H. Omukwenyi

Re: Trigger Problem

monika_vasvani7-Nov-06 16:47

monika_vasvani

7-Nov-06 16:47

Hi
I got yr solution but i have one confusion what is exactly inserted table it's already in sql server.how to define inserted table?I am not getting concept of inserted table.Pls help me and define all the details.

Thanks for yr nice help.

Thanks
monika

Re: Trigger Problem

Mairaaj Khan7-Nov-06 19:51

Mairaaj Khan

7-Nov-06 19:51

SQL SERVER TRIGGERS[^]

________________________________
Success is not something to wait for, its something to work for.

Re: Trigger Problem

vbkenya8-Nov-06 0:31

vbkenya

8-Nov-06 0:31

The inserted table is a SQL Server in-memory structure only available within triggers fired during INSERT and UPDATE operations. The table has the same structure (columns) as the table that the trigger is defined on and contains as many rows as have been inserted or updated. The inserted table contains the values that are being inserted into the base table.

For example, you have Table1 with 2 columns; [col1] and [col2] and you have defined an insert trigger on Table1. When you try to insert a row into Table1 containing the values "A" for col1 and "B" for col2, the trigger fires. Within the context of the trigger, SQL Server provides you with a temporary in-memory structure called 'inserted' which contains 2 columns; col1 and col2 and this table has 1 row containing the values "A" and "B" respectively. You can query this table just like any other table using available T-SQL constructs e.g SELECT , JOIN , UNION etc.

So to put the new data into another table, just query the 'inserted' table and insert the results wherever you want.

Just for the sake of completion, SQL Server also offers you another table called 'deleted' available in triggers fired for DELETE and UPDATE operations. It can be used in the same manner as the 'inserted' table only that it contains the values that are being deleted/modified from the base table.

Nathan H. Omukwenyi

declaration of variables

Ravi Shankar436-Nov-06 19:56

Ravi Shankar43

6-Nov-06 19:56

iam getting error when setting the value to the variable.

Re: declaration of variables

coolestCoder6-Nov-06 20:28

coolestCoder

6-Nov-06 20:28

can you post what you have written along with the error message ?

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder

Re: declaration of variables

Ravi Shankar436-Nov-06 20:52

Ravi Shankar43

6-Nov-06 20:52

ok i got the solution.i forgot to keep it in single quotes.

Re: declaration of variables

Mairaaj Khan6-Nov-06 20:50

Mairaaj Khan

6-Nov-06 20:50

Ravi Shankar43 wrote:
iam getting error when setting the value to the variable

Hi,
Suppose you have a varchar type variable, named @abc. With T-SQL you can assign value to @abc in two ways.
1- With the SET keyword

SET @abc='Its varchar typed'

2- With the SELECT keyword

SELECT @abc='Its new value'

However, with the SELECT keyword you can assingn values to multiple variables according to the following syntax.
SELECT varialbe1 = value[, variable2 = value, ...]

________________________________
Success is not something to wait for, its something to work for.

What is the Sql Query?

kumar bharat bhusanam6-Nov-06 19:15

kumar bharat bhusanam

6-Nov-06 19:15

I want to display 10th record to 20th record from a table. I have no any field to store the recordno. I am using sql Server2000.

Bharat Bhusanam

Re: What is the Sql Query?

coolestCoder6-Nov-06 20:34

coolestCoder

6-Nov-06 20:34

Hi,
If you have some ID field, you can do something like this ---
If you have emp table like this --

Emp_Id  Emp_Name  Emp_Salary
1	ABC	2500.0000
2	ACD	1000.0000
3	AND	6000.0000
4	SVN	10000.0000
5	AYK	8500.0500
6	AT	8500.0000
7	NM	8500.0000
8	AK	5000.0000
9	VD	20000.0000
10	VP	100.0000
11	CV	8500.0000

then you can do something like this--

SELECT *
FROM (SELECT TOP 3 *
FROM (SELECT TOP 5 *
FROM EMP	
ORDER BY EMP_ID ASC) AS T10
ORDER BY EMP_ID DESC) AS T5
ORDER BY EMP_ID ASC

this will return records from 3 to 5
you can change values dynamically (from program) to get desired results

hope i am clear !

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder

Re: What is the Sql Query?

PavanPareta10-Nov-06 2:24

PavanPareta

10-Nov-06 2:24

nice Query.... Rose | [Rose]

Pavan Pareta

plz solve this

Ravi Shankar436-Nov-06 19:07

Ravi Shankar43

6-Nov-06 19:07

iam selecting the date from calender control into the textbox.when iam inserting into database(sql2ooo) it is showing the time also.i dont want to display time in the frontend(datagrid).what should i do?

ravi shankar

Re: plz solve this

coolestCoder6-Nov-06 19:47

coolestCoder

6-Nov-06 19:47

hi,
use something like this

select convert(varchar,getdate(),101)

you can use your column name instead of getdate()

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder

Re: plz solve this

Ravi Shankar436-Nov-06 20:12

Ravi Shankar43

6-Nov-06 20:12

yes thank u i got it

how to access errors from sql server 2000 in vb.net

king sunny6-Nov-06 18:31

king sunny

6-Nov-06 18:31

I wnat to know how to access the sql server 2000 errors in vb.net.

Also how to handle these errors in the client place.where to save them.

Re: how to access errors from sql server 2000 in vb.net

coolestCoder6-Nov-06 19:51

coolestCoder

6-Nov-06 19:51

hi,
i think for each error that is fired from sql server you get exceptions in .net? You can handle them there.

"A good programmer is someone who looks both ways before crossing a one-way street." -- Doug Linder

coolestCoder

SQL appending result rows

xoxoxoxoxoxox6-Nov-06 12:57

xoxoxoxoxoxox

6-Nov-06 12:57

Hi,
Is there anyway to append query result rows together in SQL? For example, i want to append/concatenate all rows with same id.
Table:
id column1 column2 column3
1 a b c
1 d e f
2 g h i
2 k l m

Result:
id column1 column2 column3 column4 column5 column6
1 a b c d e f
2 g h i k l m

I'll appreciate any help.

Thanks

Re: SQL appending result rows

Stathread6-Nov-06 18:31

Stathread

6-Nov-06 18:31

Are you wanting to update the table to have "6" columns with a value in each, do you want to just view the "results" in 6 columns or do you want 1 column with all values "appended" together?

Re: SQL appending result rows

xoxoxoxoxoxox7-Nov-06 5:24

xoxoxoxoxoxox

7-Nov-06 5:24

Thank you for your response. I want to view the result in 6 columns.

Reporting Services snapshots - multiple parameters

mdipalma786-Nov-06 9:39

mdipalma78

6-Nov-06 9:39

Hope this is the right forum. If not, please feel free to direct me elsewhere...

I'm trying to build a suite of reports using Reporting Services 2005. At the most general level, there will be a report of Year-to-date sales data - pretty standard stuff; revenue, expense, net revenue, revenue per unit, etc. comparing current to previous year. The national totals will be at the top of the report. Beneath that will be the same data for each of 6 Sales regions. I have made the name of each region click-through to a similar report for that region. On the regional report, the top line is the regional totals, with the same info for each sales rep beneath it.

I'm simplifying a bit to keep the example simple. There are additional details included in groups on each of the reports which make combining them into a single report impractical.

Given the amount of calculations involved, and the fact that the data will only be updated monthly, this is a classic case where snapshots would help me display the pages more quickly.

I've got the national report running on a snapshot. The problem is with the regional report; since the click-through passes that report a parameter, I am trying to get the snapshot to run for each region. If I enter a single parameter in Properties -> Parameters, it only runs the snapshot for that region. I tried making it a multi-value parameter, but then clicking on the region name always brings me to the data for the region that is listed first, regardless of which region was clicked.

I'm hoping that there is a way that I can get the snapshots to run for each region, so the regional report will display in a timely fashion.

Thanks.

query

Ravi Shankar435-Nov-06 20:10

Ravi Shankar43

5-Nov-06 20:10

How can i get the phonenumbers starting with 98494 series from the database?Plz i need a query for this.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.