Re: OT: SQL trigger question
"Syed.Alam" <[email protected]> Fri, 2 Aug 2002 14:52:05 -0500
| Newsgroups | gmane.comp.windows.devel.oledb.devel |
|---|---|
| Message-ID | <[email protected]> |
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C23A5E.13BFF800
Content-Type: text/plain;
charset="iso-8859-1"
a cursor will do !
so stupid of me.
Thanks anyway
-----Original Message-----
From: Syed.Alam [mailto:[email protected]]
Sent: Friday, August 02, 2002 2:32 PM
To: [email protected]
Subject: [OLEDB_DEV] OT: SQL trigger question
Hi
Sorry for the OT (somewhat related), and excuse me of this is really a dumb
question.
I have an INSERT trigger on table tab. The insert operation on this table
is like
insert into tab (col1, col2 )
select A.col1, 0 from tab A
where A.col2 = 2
The trigger is like:
CREATE TRIGGER tr_tab1
ON tab1
FOR INSERT
AS
BEGIN
DECLARE @col1 int
DECLARE @col2 int
SELECT @col1 = col1,
@col2 = col2
FROM inserted
if ( @col2 = 0 )
insert into tab2 values (@col1+1)
End
The trigger is called once with a rowset containing all rows to be included.
How can I parse thru all the rows (just like we do in ADO, like MoveNext()
)?
Right now, as u can see in the trigger, only the last row is being dealt
with. I can do like
insert into tab2 (...)
select ... from inserted
but i want to parese thru the resultset (in a loop)
Thanks
You can read messages from the OLEDB_DEV archive, unsubscribe from
OLEDB_DEV, or subscribe to other DevelopMentor lists at
http://discuss.develop.com.
You can read messages from the OLEDB_DEV archive, unsubscribe from OLEDB_DEV,
or subscribe to other DevelopMentor lists at http://discuss.develop.com.
------_=_NextPart_001_01C23A5E.13BFF800
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.50.4522.1800" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=477545119-02082002><FONT face=Verdana color=#0000ff size=2>a
cursor will do !</FONT></SPAN></DIV>
<DIV><SPAN class=477545119-02082002><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=477545119-02082002><FONT face=Verdana color=#0000ff size=2>so
stupid of me. </FONT></SPAN></DIV>
<DIV><SPAN class=477545119-02082002><FONT face=Verdana color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=477545119-02082002><FONT face=Verdana color=#0000ff
size=2>Thanks anyway</FONT></SPAN></DIV>
<BLOCKQUOTE>
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Syed.Alam
[mailto:[email protected]]<BR><B>Sent:</B> Friday, August 02, 2002 2:32
PM<BR><B>To:</B> [email protected]<BR><B>Subject:</B> [OLEDB_DEV]
OT: SQL trigger question<BR><BR></FONT></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2>Hi</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>Sorry for the OT
(somewhat related), and excuse me of this is really a dumb
question.</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>I have
an INSERT trigger on table tab. The insert operation on this
table is like </FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>insert into
tab (col1, col2 )<BR>select A.col1, 0 from tab A<BR>where A.col2 =
2<BR></FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>The trigger is
like:</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>CREATE TRIGGER
tr_tab1<BR>ON tab1<BR>FOR INSERT<BR>AS</FONT></SPAN></DIV>
<DIV><FONT face=Verdana size=2></FONT> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2>BEGIN<BR> DECLARE @col1 int<BR> DECLARE
@col2 int</FONT></SPAN></DIV>
<DIV><FONT face=Verdana size=2></FONT> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2><BR> SELECT @col1 = col1,<BR> @col2 =
col2<BR> FROM inserted<BR> <BR> if ( @col2 = 0
)<BR> insert into tab2 values
(@col1+1)<BR>End</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>The trigger is
called once with a rowset containing all rows to be included. How can I parse
thru all the rows (just like we do in ADO, like MoveNext() )?
</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>Right now, as u
can see in the trigger, only the last row is being dealt with. I can do
like</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>insert into tab2
(...)</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>select ...
from inserted</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana size=2>but i want to
parese thru the resultset (in a loop)</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2>Thanks</FONT></SPAN></DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=716052719-02082002><FONT face=Verdana
size=2> </DIV></FONT></SPAN>You can read messages from the OLEDB_DEV
archive, unsubscribe from OLEDB_DEV, or subscribe to other DevelopMentor lists
at http://discuss.develop.com. </BLOCKQUOTE></BODY></HTML>
You can read messages from the OLEDB_DEV archive, unsubscribe from OLEDB_DEV,
or subscribe to other DevelopMentor lists at http://discuss.develop.com.
------_=_NextPart_001_01C23A5E.13BFF800--