Re: ICommandText.Execute on table with trigger returns wrong RowsAffected?

Thank You for your replay Erland!
That's what I have done - I'm using IMultipleResults and the last one (after
all trigger statements are executed) is the one I need.
But I'm trying to understand why Execute returns value of RowsAffected not
related to the statement I execute (there wasn't problem with DB-Lib with
this). Maybe there is some property I can set or specific SP needed?

mocarts

"Erland Sommarskog" <esquel@xxxxxxxxxxxxx> wrote in message
news:Xns9671E34C0DEE5Yazorman@xxxxxxxxxxxx
> mocarts (helmuts@xxxxxxxxxxxx) writes:
> > I'm trying to understand is this feature or bug when inserting, updating
> > table with trigger, RowsAffected parameter is set to last trigger's
> > executed statement affected rows count.
> > example:
> > stmt: INSERT INTO TEST (PK, VAL) VALUES (1, 2)
> >
> > and trigger:
> > create trigger TESTBUG on TEST
> > for insert, update, delete
> > as
> > begin
> > UPDATE TEST SET PK=PK WHERE 1=2
> > end
> >
> > in this case when executing stmt RowsAffected returns 0. without trigger
> > or with SET NOCOUNT ON in the trigger there is correct result - 1 how I
> > can make sure that smt is succeeded? especially that is important in
> > case of update (examp. UPDATE TEST SET VAL=VAL+1 WHERE PK=1)..
>
> You could have a SET NOCOUNT ON in the trigger, to suppress that rowcount.
> However, that helps you little if you don't have full control over the
> database.
>
> The other solution is to use IMultipleResults. The you will get
> one rowset object for each rowcount, and you will have to know that
> the first rowcount is the one you are looking for. You still will
> have to sift through the remaining rowsets.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp


.

Relevant Pages

  • [NEWS] Multiple Vulnerabilities in Oracle Database (Trigger, Extproc, Wrapped Procedures, PL/SQL Inj
    ... The following security advisory is sent to the securiteam mailing list, and can be found at the SecuriTeam web site: http://www.securiteam.com ... Multiple vulnerabilities were discovered in the Oracle database server. ... Oracle Trigger Abuse ... written in PL/SQL and execute with the privileges of the definer/owner. ...
    (Securiteam)
  • Re: Stored procedure/trigger and scripts
    ... must have been the trigger that locked up the table. ... SQL Server has permissions to execute xp_cmdshell. ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: Stored procedure/trigger and scripts
    ... I just ran some additional tests and it looks like my trigger is fine. ... SQL Server has permissions to execute xp_cmdshell. ... >> client to change their password they have to call the "Client Relations" ...
    (microsoft.public.sqlserver.programming)
  • Re: Best Practice - xp_cmdshell question
    ... execute from the trigger and use an alternate method. ... objects in an sa-owned database. ... >> without direct user execute permissions by changing your user database ...
    (microsoft.public.sqlserver.security)
  • Re: Use INSERTED and DELETED in SP
    ... Now an administrator can call my "generator SP" that will ... then generate a new trigger for the changed tab. ... thx again for your answer and have a good weekend. ... then execute. ...
    (microsoft.public.sqlserver.programming)