Board index » delphi » What's wrong here?

What's wrong here?

====> This is a Stored Procedure:
@ID char(11),
@Name char (25),
@ItCode smallint,
@Qty smallint
 AS
begin transaction
    insert YFRUsers values (@ID,@Name)  
    insert YFRSells values (@ItCode,@ID,@Qty)
commit transaction
GO
--------------------------------------------------------
* Inside YFRSells I have a Trigger that cheks if Qty > 3, if so,
  it fires:
====>  This is the code of the trigger: (for inserts and updates)
if (select Qty from inserted) > 3
begin
  rollback transaction
  raiserror ('It cannot be more than 3',16,1)
end
------------------------------------------------------------

====> ... and later, from Delphi:

  ADOStoredProc1.Parameters [1].Value := '77777777777';
  ADOStoredProc1.Parameters [2].Value := 'WhoCares';
  ADOStoredProc1.Parameters [3].Value := 1;
// Next line should fires the trigger when ExecProc called.
// (Shouldn't it?)
  ADOStoredProc1.Parameters [4].Value := 5;  // Qty
  ADOStoredProc1.Prepared := True;
  ADOStoredProc1.ExecProc;          // Bum !!!

* If I execute SP from SQL Analyzer it shows me the "raiserror".
Now, here is my problem: when I call this from Delphi (as shown
above), if Qty is greater than 3 I can't see the "raiserror" I
have in the trigger.
Why?

 

Re:What's wrong here?


    A rollback in a trigger terminates the batch so it will never reach the
raiserror. Give the raiserror before the rollback.
--
Sunil Furtado
New Mangalore Port Trust
Quote
Yuri <y...@informaticos.com> wrote in message news:3b4a4273$1_1@dnews...

> ====> This is a Stored Procedure:
> @ID char(11),
> @Name char (25),
> @ItCode smallint,
> @Qty smallint
>  AS
> begin transaction
>     insert YFRUsers values (@ID,@Name)
>     insert YFRSells values (@ItCode,@ID,@Qty)
> commit transaction
> GO
> --------------------------------------------------------
> * Inside YFRSells I have a Trigger that cheks if Qty > 3, if so,
>   it fires:
> ====>  This is the code of the trigger: (for inserts and updates)
> if (select Qty from inserted) > 3
> begin
>   rollback transaction
>   raiserror ('It cannot be more than 3',16,1)
> end
> ------------------------------------------------------------

> ====> ... and later, from Delphi:

>   ADOStoredProc1.Parameters [1].Value := '77777777777';
>   ADOStoredProc1.Parameters [2].Value := 'WhoCares';
>   ADOStoredProc1.Parameters [3].Value := 1;
> // Next line should fires the trigger when ExecProc called.
> // (Shouldn't it?)
>   ADOStoredProc1.Parameters [4].Value := 5;  // Qty
>   ADOStoredProc1.Prepared := True;
>   ADOStoredProc1.ExecProc;          // Bum !!!

> * If I execute SP from SQL Analyzer it shows me the "raiserror".
> Now, here is my problem: when I call this from Delphi (as shown
> above), if Qty is greater than 3 I can't see the "raiserror" I
> have in the trigger.
> Why?

Re:What's wrong here?


    Sorry this is not correct. The rollback issued in a trigger will execute
remaining statements. So, the problem here lies elsewhere.
--
Sunil Furtado
New Mangalore Port Trust

Quote
Sunil Furtado <su...@furtado.de> wrote in message news:3b4aac63_2@dnews...
>     A rollback in a trigger terminates the batch so it will never reach
the
> raiserror. Give the raiserror before the rollback.
> --
> Sunil Furtado
> New Mangalore Port Trust

> Yuri <y...@informaticos.com> wrote in message news:3b4a4273$1_1@dnews...

> > ====> This is a Stored Procedure:
> > @ID char(11),
> > @Name char (25),
> > @ItCode smallint,
> > @Qty smallint
> >  AS
> > begin transaction
> >     insert YFRUsers values (@ID,@Name)
> >     insert YFRSells values (@ItCode,@ID,@Qty)
> > commit transaction
> > GO
> > --------------------------------------------------------
> > * Inside YFRSells I have a Trigger that cheks if Qty > 3, if so,
> >   it fires:
> > ====>  This is the code of the trigger: (for inserts and updates)
> > if (select Qty from inserted) > 3
> > begin
> >   rollback transaction
> >   raiserror ('It cannot be more than 3',16,1)
> > end
> > ------------------------------------------------------------

> > ====> ... and later, from Delphi:

> >   ADOStoredProc1.Parameters [1].Value := '77777777777';
> >   ADOStoredProc1.Parameters [2].Value := 'WhoCares';
> >   ADOStoredProc1.Parameters [3].Value := 1;
> > // Next line should fires the trigger when ExecProc called.
> > // (Shouldn't it?)
> >   ADOStoredProc1.Parameters [4].Value := 5;  // Qty
> >   ADOStoredProc1.Prepared := True;
> >   ADOStoredProc1.ExecProc;          // Bum !!!

> > * If I execute SP from SQL Analyzer it shows me the "raiserror".
> > Now, here is my problem: when I call this from Delphi (as shown
> > above), if Qty is greater than 3 I can't see the "raiserror" I
> > have in the trigger.
> > Why?

Other Threads