rows as columns

is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable

Rownumber()

I am trying to write a stored procedure to be used for custompaging and I get error with the below SP.
"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

What am I doing wrong?

CREATEPROCEDURE dbo.GetDealerSP

(
@.startRowIndexint,
@.maximumRowsint
)
As
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax
From
(
SELECT installersemaid,dealerid,[name],address1,address2,city,[state],
zip,phone,fax,ROW_NUMBER()OVER(ORDERBY [name]DESC)AS Rowbank
FROM dealerenrollment)as DealerWithRowNumbers
WHERE Rowbank> @.startRowIndexAND RowRank<=(@.startRowIndex+ @.maximumRows)
Go

Hi bhavin78,

bhavin78:

"Msg 207, Level 16, State 1, Procedure GetDealersPagedSP, Line 14 Invalid column name 'RowRank'."

...

CREATEPROCEDURE dbo.GetDealerSP

...

WHERE Rowbank> @.startRowIndexANDRowRank<=(@.startRowIndex+ @.maximumRows)
Go

Looks like it's a typo, that RowRank should be Rowbank according to the rest of your query.

Personally, I'd change the three instances of Rowbank to RowRank, as it's a little closer to describing the columns contents (actually, calling it RowNumber would be my first choice ;) ).

I hope that helps.

|||

--zip,phone,fax, ROW_NUMBER() OVER(ORDER BY [name] DESC)ASRowbank

You have used Rowbank not RowRank check it once

RowChanged

I'm trying to put a fix in on a web page I did awhile back. The email occasionally goes down on it. So I was going to write emails to a SQL Server Table, then every two minutes check for entries in the table, and send out the email if any exist, on success remove the record. I've done this. But running a thread for the life of the application and querrying every 2 minutes seems a little overkill. How can I, with C#/ASP.NET get an event for when a record is added to the database, so I can start the thread then, and also end it after it has successfully sent out all queued emails?

I've seen RowChanged, but not a lot of documentation on it's use, ie, does there have to be an open connection the whole time? Does there have to be a class overridden to catch the event?You should use a SQL Trigger.|||Actually, I'd probably create a SQL Server Agent Job that did that. You could schedule it to fire every couple of minutes. It's a service that's probably already running.

David

row_id in sql server....

Hi all,
i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following table:(the query can be like select bill_id,name from myTable order by name)
Bill_id name
25 abc
18 def
26 ghi
23 adfd

but my result should look like:
Row_Id Bill_id name
1 25 abc
2 18 def
3 26 ghi
4 23 adfd

n this Row_Id should be generated automatically...even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general???
any help will be greatly appreciated.
Regards.create table t(bill_id int, name varchar(10))

insert into t values(25, 'abc')
insert into t values(18, 'def')
insert into t values(26, 'ghi')
insert into t values(23, 'adfg')

select Row_ID=count(*), a1.bill_id, a1.name
from t a1, t a2
where a1.bill_id >= a2.bill_id
group by a1.bill_id, a1.name
order by 1

Row_ID bill_id name
-------
1 18 def
2 23 adfg
3 25 abc
4 26 ghi|||Thanks a lot...it really WORKED...
regards.sql

row to column

hello

TableT1 has

T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2

How can I write my view to report these in

MyID, MyDate1, MyNote1, MyCharge1

MyID, MyDate2,MyNote2, MyCharge2

format?

You could use the following SQL Statement to make this happen:

select myid,mydate1,mynote1,mychange1
from MyInfo
union
select myid,mydate2,mynote2,mychange2
from MyInfo
order by myid

It would return appear something like the following:

MyID MyDate MyNote MyChange

1 2006-08-07 00:00:00.000 Called Dealer For Customer Service Changed his mail cost for 15 to 20
1 2006-08-08 00:00:00.000 Faxed Information to Dealer Changed Status to Enrolled
2 2006-08-04 00:00:00.000 Enrolling of Dealer Enrolled Dealer in Oil Change Mail Piece
2 2006-08-09 00:00:00.000 Dealer Called Changed to Tune-Up Mail Piece

Let me know if this works for you.

crusso

|||

You should actually normalize your table so that it is easier to work with. You can do one of the following with your existing table structure:

select t.MyID

, case r.n when 1 then MyDate1 when 2 then MyDate2 end as MyDate

, case r.n when 1 then MyNote1 when 2 then MyNote2 end as MyNote

, case r.n when 1 then MyCharge1 when 2 then MyCharge2 end as MyCharge

from T1 as t

cross join (select 1 union all select 2) as r(n)

-- or

select t.MyID, t.MyDate1 as MyDate, t.MyNote1 as MyNote, t.MyCharge1 as MyCharge

from T1 as t

union all

select t.MyID, t.MyDate2 as MyDate, t.MyNote2 as MyNote, t.MyCharge2 as MyCharge

from T1 as t

row to column

hello
Table T1 has
T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
How can I write my view to report these in
MyID, MyDate1, MyNote1, MyCharge1
MyID, MyDate2,MyNote2, MyCharge2
format?Try this:
How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574
(Or wait for Steve's Post...)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:FB0BC5F5-5EF1-4AA7-A3A1-BE4FA9A0010F@.microsoft.com...
> hello
> Table T1 has
> T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
> How can I write my view to report these in
> MyID, MyDate1, MyNote1, MyCharge1
> MyID, MyDate2,MyNote2, MyCharge2
> format?
>|||Thanks you for your reply, it seems I need the exact opposite of the case
given there, how should I write my sql, any idea?
"Arnie Rowland" wrote:
> Try this:
> How to rotate a table in SQL Server
> http://support.microsoft.com/default.aspx?scid=kb;en-us;175574
> (Or wait for Steve's Post...)
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
> news:FB0BC5F5-5EF1-4AA7-A3A1-BE4FA9A0010F@.microsoft.com...
> >
> > hello
> > Table T1 has
> > T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
> >
> > How can I write my view to report these in
> > MyID, MyDate1, MyNote1, MyCharge1
> > MyID, MyDate2,MyNote2, MyCharge2
> > format?
> >
> >
>
>|||create view T_All
as
select MyID, MyDate1 as MyDate, MyNote1 as MyNote, MyCharge1 as
MyCharge
from T1 with(nolock)
where MyDate1 is not null
union all
select MyID, MyDate2, MyNote2, MyCharge2
from T1 with(nolock)
where MyDate2 is not null

Row level trigger

Hi,

Is there any row-level trigger available in SQL server(in oracle is does)

my problem is ..I am trying to write a trigger which updates all the items

But it give me error:

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.

My statement is : SET @.var_item=(SELECT item_no FROM deleted)

How can i make it possible?
Note: I am trying convert Oracle trigger to SQL Server triggerWhy don't you post he Oracle Trigger...

inserted and deleted are called what again in Oracle?

oh yeah old and new...

It's not FOR EACH as in Oracle

Is there FOR ALL?

Don't know only got up to 8i...

But SQL Server is set based...|||Hi,
This oracle trigger and I want to translate in SQL SERVER

DECLARE
DB_ID NUMBER;
CQE NUMBER;
PC NUMBER;
item VARCHAR2(7);
AMT_PAID NUMBER(12,2);
AMT_RET NUMBER(12,2);
ITEM_NEW VARCHAR2(1);
quant number(12,3);
fiyr NUMBER;
BEGIN
/* Find the non null values to be used for propagating changes */
if :new.db_contract is not null then
db_id := :new.db_contract;
cqe := :new.cqe_numb;
pc := :new.pc_code;
item := :new.item_no;
fiyr := :new.fy_item;
else
db_id := :old.db_contract;
cqe := :old.cqe_numb;
pc := :old.pc_code;
item := :old.item_no;
fiyr := :old.fy_item;
end if;
amt_paid := nvl(:new.amt_paid_item,0) -nvl(:old.amt_paid_item,0);
amt_ret := nvl(:new.amt_ret_item,0) -nvl(:old.amt_ret_item,0);
quant := nvl(:new.quantity,0) -nvl(:old.quantity,0);
/* RAISE_APPLICATION_ERROR(-20501,'CHEK1 '||DB_ID||' CHEK2 '||PC
||' CHEK3 '||ITEM); */
SELECT NEW_ITEM INTO ITEM_NEW
FROM VALID_ITEM
WHERE DB_CONTRACT = DB_ID
AND PC_CODE = PC
AND ITEM_NO = ITEM;
update ae_contract
set amt_paid_contr = nvl(amt_paid_contr,0) + amt_paid,
amt_ret_contr = nvl(amt_ret_contr,0) + amt_ret
where db_contract = db_id;
if item_new = 'N' then
update vendor
set used_amt = nvl(used_amt,0) + amt_paid + amt_ret
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=db_id);
end if;
update enc_det
set amt_paid_fy = nvl(amt_paid_fy,0) + amt_paid,
amt_ret_fy = nvl(amt_ret_fy,0) + amt_ret
where db_contract = db_id
and pc_code = pc
and fy = fiyr;
update valid_item
set tamt_ret_item = nvl(tamt_ret_item,0) + amt_ret,
tamt_paid_item = nvl(tamt_paid_item,0) + amt_paid,
qtd = nvl(qtd,0) + quant
where db_contract = db_id
and pc_code = pc
and item_no = item;
end;|||Where's this stuff?

CREATE OR REPLACE TRIGGER hist_enr_dpnd_benef_tr
BEFORE DELETE or UPDATE on enr_dpnd_benef
FOR EACH ROW
IF DELETING THEN
INSERT into enr_dpnd_benef_h
VALUES(:old.EMPLID
,:old.DEPENDENT_BENEF
...

So you check to see if new is null and then assume it's a delete?

You need to join the virtual tables

Trigger syntax is...

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

And the interogation of the virtual tables tell you what happended..in this case, I'm checking for an update here...|||I translated in sql server this way...is it write?

CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@.var_DB_contract INTEGER,
@.var_CQE INTEGER,
@.var_PC INTEGER,
@.var_item VARCHAR(7),
@.var_AMT_PAID INTEGER,
@.var_AMT_RET INTEGER,
@.var_ITEM_NEW VARCHAR(1),
@.var_quant DECIMAL,
@.var_fiyr INTEGER,
@.var_amt_result INTEGER,
@.var_amt_ret_result INTEGER,
@.var_amt_old INTEGER,
@.var_amt_ret_old INTEGER,
@.var_quant_result INTEGER,
@.var_quant_new INTEGER,
@.var_quant_old INTEGER,
@.Item_new VARCHAR(7),
@.var_chk varchar(1)

set @.var_db_contract =(Select db_contract from inserted)
IF @.var_db_contract IS NOT NULL
BEGIN
SET @.var_db_contract=(SELECT distinct db_contract FROM inserted)
SET @.var_cqe=(SELECT distinct cqe_numb FROM inserted)
-- SET @.var_pc=(SELECT pc_code FROM inserted)
-- SET @.var_item=(SELECT item_no FROM inserted)
-- SET @.var_fiyr=(SELECT fy_item FROM inserted)
set @.var_chk ="Y"
END
ELSE
BEGIN
SET @.var_db_contract=(SELECT distinct db_contract FROM deleted)
SET @.var_cqe=(SELECT distinct cqe_numb FROM deleted)
-- SET @.var_pc=(SELECT distinct pc_code FROM deleted)
--SET @.var_item=(SELECT item_no FROM deleted)
-- SET @.var_fiyr=(SELECT fy_item FROM deleted)
set @.var_chk="N"
END
SET @.var_amt_paid=(SELECT amt_paid_item FROM inserted)
SET @.var_amt_old=(SELECT amt_paid_item FROM deleted)
SET @.var_amt_result =ISNULL(@.var_amt_paid,0) - ISNULL(@.var_amt_old,0)

SET @.var_amt_ret = (SELECT amt_ret_item from inserted)
SET @.var_amt_ret_old=(SELECT amt_ret_item from deleted)
SET @.var_amt_ret_result = isnull(@.var_amt_ret,0) - isnull(@.var_amt_ret_old,0)

SET @.var_quant_new = (SELECT quantity from inserted)
SET @.var_quant_old =(SELECT quantity from deleted)
SET @.var_quant_result = isnull(@.var_quant_new,0) - isnull(@.var_quant_old,0)
if @.var_chk="Y"
begin
declare val_cur cursor for
select pc_code,item_no,fy_item from inserted
open val_cur
Fetch next from val_cur
into @.var_pc,@.var_item,@.var_fiyr
while @.@.fetch_status= 0
begin
SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_item
end
close val_cur
deallocate val_cur

end

else
begin
declare val_cur_del cursor for
select pc_code,item_no,fy_item from deleted
open val_cur_del
Fetch next from val_cur_del
into @.var_pc,@.var_item,@.var_fiyr
while @.@.fetch_status= 0
begin

SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_item
end
close val_cur_del
deallocate val_cur_del

end|||I thought I had mentioned it...

BUT...

You have to think in terms of SET Processing...

You can't just say SELECT @.x = col1 FROM inserted

It will give you the last row of data...

But SQL Server is trying to process ALL of the rows based on the event

So yo uneed to do a join to the virtual tables...|||Brett,

Can you give me any example of virtual binding based on my trigger?

I shall really appreciate.|||Here's a sample of one of mine...

It's for UPDATES AND DELETES..

The existance check see's which one it is...the first is for the updates..

Notice the join to the virtual table

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END|||Hi Brett,

Thank you very much for your cooperation. I changed the code and now I think it would work.

Any comments pleae let me know khuram@.hotmail.com

CREATE TRIGGER [PROP_AMT] ON [dbo].[cqe_item]
FOR INSERT, UPDATE, DELETE
AS
DECLARE
@.var_DB_contract INTEGER,
@.var_CQE INTEGER,
@.var_PC INTEGER,
@.var_item VARCHAR(7),
@.var_AMT_PAID INTEGER,
@.var_AMT_RET INTEGER,
@.var_ITEM_NEW VARCHAR(1),
@.var_quant DECIMAL,
@.var_fiyr INTEGER,
@.var_amt_result INTEGER,
@.var_amt_ret_result INTEGER,
@.var_amt_old INTEGER,
@.var_amt_ret_old INTEGER,
@.var_quant_result INTEGER,
@.var_quant_new INTEGER,
@.var_quant_old INTEGER,
@.Item_new VARCHAR(7),
@.var_chk varchar(1)

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
set @.var_db_contract =(Select db_contract from inserted)
IF @.var_db_contract IS NOT NULL
BEGIN
SET @.var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_fiyr=(SELECT a.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @.var_chk ="Y"
END
ELSE
BEGIN
SET @.var_db_contract=(SELECT a.db_contract FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_cqe=(SELECT a.cqe_numb FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_pc=(SELECT a.pc_code FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_item=(SELECT a.item_no FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_fiyr=(SELECT b.fy_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
set @.var_chk="N"
END
SET @.var_amt_paid=(SELECT a.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_amt_old=(SELECT b.amt_paid_item FROM inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no )
SET @.var_amt_result =ISNULL(@.var_amt_paid,0) - ISNULL(@.var_amt_old,0)

SET @.var_amt_ret = (SELECT a.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_amt_ret_old=(SELECT b.amt_ret_item from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_amt_ret_result = isnull(@.var_amt_ret,0) - isnull(@.var_amt_ret_old,0)

SET @.var_quant_new = (SELECT a.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_quant_old =(SELECT b.quantity from inserted a,deleted b where a.db_contract = b.db_Contract and a.cqe_numb = b.cqe_numb and a.pc_code = b.pc_code and a.item_no = b.item_no)
SET @.var_quant_result = isnull(@.var_quant_new,0) - isnull(@.var_quant_old,0)
SELECT @.item_new = new_item
FROM VALID_ITEM
WHERE DB_CONTRACT = @.var_db_contract
AND PC_CODE = @.var_PC
AND ITEM_NO = @.var_ITEM

UPDATE ae_contract
set amt_paid_contr = isnull(amt_paid_contr,0) +@.var_amt_result,
amt_ret_contr = isnull(amt_ret_contr,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract

IF @.item_new = 'N'
BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result
where db_vendor = (select gen_contr from ae_contract
where ae_contract.db_contract=@.var_db_contract);
END
UPDATE enc_det
set amt_paid_fy = isnull(amt_paid_fy,0) + @.var_amt_result,
amt_ret_fy = isnull(amt_ret_fy,0) + @.var_amt_ret_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and fy = @.var_fiyr

UPDATE valid_item
set tamt_ret_item = isnull(tamt_ret_item,0) + @.var_amt_ret_result,
tamt_paid_item = isnull(tamt_paid_item,0) + @.var_amt_result,
qtd = isnull(qtd,0) + @.var_quant_result
where db_contract = @.var_db_contract
and pc_code = @.var_pc
and item_no = @.var_item|||Nope you're still not there

This

set @.var_db_contract =(Select db_contract from inserted)

Won't work correctly

This should paint the picture for you...cut and paste it in QA

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
CREATE TABLE myTable00(Col1 int)
GO

CREATE TRIGGER myTriger99 ON myTable99 FOR INSERT
AS
DECLARE @.Col1 int
SELECT @.Col1 = Col1 FROM inserted
INSERT INTO myTable00(Col1) SELECT @.Col1
GO

INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

Row Level Trigger

How can i write a trigger which gets fired for each row that is being deleted

i.e. Row level TriggerI tried something like this

ALTER TRIGGER [dbo].[Del_Msg] ON [Seat27].[dbo].[Email_Messages] AFTER DELETE&......

it gets executed once for the triggering SQL statement

Oracle provides row and statement level triggers.

Sql Server does not. Instead, it provides statement level triggers with access to row-level information.

There are two dummy tables, @.inserted and @.deleted available inside your trigger.

If a record is in both tables, it was updated. Otherwise, it was inserted or deleted as obvious.

Your trigger needs to perform whatever row-oriented tasks it needs to do using the dummy tables for input.

|||Thanx

Row id in sql server....

Hi all,
i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following table:(the query can be like select bill_id,name from myTable order by name)

Bill_id

name

25

abc

18

def

26

ghi

23

adfd

but my result should look like:

Row_Id

Bill_id

name

1

25

abc

2

18

def

3

26

ghi

4

23

adfd

n this Row_Id should be generated automatically....even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general?
any help will be greatly appreciated.
Regards.ok,i found the solution....here it is:

select rank=count(*), a1.au_lname, a1.au_fname
from authors a1, authors a2
where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
group by a1.au_lname, a1.au_fname
order by 1
|||http://www.aspfaq.com/2427
wrote in message

news:4cd06c7a-358b-47eb-943e-e4e74d2d52b8@.discussions.microsoft.com...

> Hi all,

> i have a task that whenever i will write a select query then when the

> result comes then i should be able to write a column that should write

> the ROW-ID of these rows. I mean for example i write a select query that

> gives me the following table:(the query can be like select bill_id,name

> from myTable order by name)

>

> Bill_id

>

> name

>

> 25

>

> abc

>

> 18

>

> def

>

> 26

>

> ghi

>

> 23

>

> adfd

>

>

> but my result should look like:

>

> Row_Id

>

> Bill_id

>

> name

>

> 1

>

> 25

>

> abc

>

> 2

>

> 18

>

> def

>

> 3

>

> 26

>

> ghi

>

> 4

>

> 23

>

> adfd

>

> n this Row_Id should be generated automatically....even if when i will

> write order by name in reverse order then also it should write the

> row_id as 1,2,3,4 same increasing order...n this automatic function i

> have to make...i cant understand..if there exist some procedure or

> function that already does this...or how can i just find the simple

> ROW_ID in general?

> any help will be greatly appreciated.

> Regards.

>

>

row count

I want to get a row count at the end of a report that I am running. Is there
a way to do this, or do I have to write the code? If I have to write it, can
anyone give me a how to guide on writing .net components for reporting
services?
--
www.duanelawrence.comCheck the CountRows() function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_0k6r.asp
E.g.
=CountRows("DataSetName")
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"duanelawrence.com" <duanelawrencecom@.discussions.microsoft.com> wrote in
message news:A7A14867-F57E-4C54-B1DF-A3A70FE186FE@.microsoft.com...
> I want to get a row count at the end of a report that I am running. Is
there
> a way to do this, or do I have to write the code? If I have to write it,
can
> anyone give me a how to guide on writing .net components for reporting
> services?
> --
>
> www.duanelawrence.com
>