question about process order

Hi all,
I have a trigger for update on a table. It does a whole bunch of things,
then calls a SP. I want to then check something afet the SP is completed. To
check this out I put a print statement after the line that called the SP
exec calcdeductables @.claimid, 1
print 'Check SIP'
The SP, by nature update multiple rows in child tables, as A result I would
expect the following:
(1 row(s) affected)
(1 row(s) affected)
(4 row(s) affected)
But with the print statement I am gettingh
(1 row(s) affected)
(1 row(s) affected)
Check SIP
(4 row(s) affected)
Should'nt the Check sip statement come last
Thanks
RobertRobert Bravery (me@.u.com) writes:
> I have a trigger for update on a table. It does a whole bunch of things,
> then calls a SP. I want to then check something afet the SP is
> completed. To check this out I put a print statement after the line that
> called the SP
> exec calcdeductables @.claimid, 1
> print 'Check SIP'
> The SP, by nature update multiple rows in child tables, as A result I
> would expect the following:
> (1 row(s) affected)
> (1 row(s) affected)
> (4 row(s) affected)
> But with the print statement I am gettingh
> (1 row(s) affected)
> (1 row(s) affected)
> Check SIP
> (4 row(s) affected)
> Should'nt the Check sip statement come last
A more interesting question is from where you got that @.claimid. If you
do
SELECT @.claimid = claimid FROM inserted
then you need to rewrite your trigger. A trigger fires once per statement,
and thus "inserted" can include more than more than one row.
If you believe that you know that your table will ever be subject to
single-row inserts and updates, add this to your trigger:
IF @.@.rowcount > 1
BEGIN
RAISERROR('Multi-row operations not supported!, 16, 1)
ROLLBACK TRANSACTION
RETURN
END
If you need to support multi-row operations, you can run a cursor over
"inserted" (and maybe this is what you do already). However, since a
trigger runs in the context of a transaction defined by the statement
that defined it, it is not a good idea to start looping in a trigger, as
this could cause contention issues.
The remedy would be to perform the updates directly the in trigger, in a
set-based fashion rather than running one by one from your procedure. If
you do that your question about output may be become a moot point.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Does the procedure end with that PRINT statement?
ML
http://milambda.blogspot.com/|||HI Erland
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97729620C5F6DYazorman@.127.0.0.1...
> Robert Bravery (me@.u.com) writes:
> A more interesting question is from where you got that @.claimid. If you
> do
> SELECT @.claimid = claimid FROM inserted
> then you need to rewrite your trigger. A trigger fires once per statement,
> and thus "inserted" can include more than more than one row.
> If you believe that you know that your table will ever be subject to
> single-row inserts and updates, add this to your trigger:
Yes I do get @.claimid from the inserted table. But it will alwaus be only
one record that is update. Just the nature of the operation.
Ok so then I need to qualify some things in my mind.
If a update trigger is ona table, and that table will always only have one
row updated at a time, the update trigger would fire, there is now an
inserted table, with the values on the update row in it. Now that trigger
then updates another table.
Do you no get a different inserted table, or is the same inserted table the
one created by the original insert, used.
Thanks for the single-row inserts little guidence you gave there
What I basically whant to do is that the update trigger on claims, updates
another table, which has say 4 rowse to update. Those four rows are related
to claim table. After that related table and its four rows are updates, I
want to perform and agregate check. All this will do is to notify the user
of an above or below threshold.
Thanks
Robert|||Robert Bravery (me@.u.com) writes:
> Yes I do get @.claimid from the inserted table. But it will alwaus be only
> one record that is update. Just the nature of the operation.
That's alright. But add an assertion to state that this is the case, to
prevent accidents.

> Ok so then I need to qualify some things in my mind.
> If a update trigger is ona table, and that table will always only have one
> row updated at a time, the update trigger would fire, there is now an
> inserted table, with the values on the update row in it. Now that trigger
> then updates another table.
> Do you no get a different inserted table, or is the same inserted table
> the one created by the original insert, used.
That's a different one. "inserted" and "deleted" are virtual tables, and
they are only visible within the trigger, nowhere else. So there is no
risk for confusion.

> What I basically whant to do is that the update trigger on claims,
> updates another table, which has say 4 rowse to update. Those four rows
> are related to claim table. After that related table and its four rows
> are updates, I want to perform and agregate check. All this will do is
> to notify the user of an above or below threshold.
I'm not sure that I understand exactly. I assumed that the PRINT was part
of some debug output. Are you now saying that the output is intended for
user consumption?
Generally, triggers are not supposed to generate any output (save for
error messages from integrity checks). It's perfectly legal to so, but
generally client code usually see INSERT, UPDATE, DELETE as operations
that do not delete data. Of course, if you code your application
accordingly, you can have output from the trigger. Still, I would consider
a different solution, if this is the case.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||HI Erland,
> That's a different one. "inserted" and "deleted" are virtual tables, and
> they are only visible within the trigger, nowhere else. So there is no
> risk for confusion.
Ok thanks, that makes it easier to understand

> I'm not sure that I understand exactly. I assumed that the PRINT was part
> of some debug output. Are you now saying that the output is intended for
> user consumption?
I did put it there for degug purposes, because I could not get what I wanted
correctly

> Generally, triggers are not supposed to generate any output (save for
> error messages from integrity checks). It's perfectly legal to so, but
> generally client code usually see INSERT, UPDATE, DELETE as operations
> that do not delete data. Of course, if you code your application
> accordingly, you can have output from the trigger. Still, I would consider
> a different solution, if this is the case.
Basically the output would only be a type of informational message
ok in psudo code
the SP would update a table called liability, based on amounts entered into
the claim table.
After the SP has update the needed rows, I would like, to perhaps call
another SP, so
declare @.liab numeric(12,2), @.ded numeric(12,2)
select @.liab=sum(ammount) from claimliability where claimid = 10
select @.ded = ammount from ClaimDeductables where claimid = 10 and clmdedid=
70
if @.liab<@.ded
raiserror('Claim does not partake in SIP', 10,1)
Is this feasable, or am I going about it the wrong way
Thanks
Robert|||HI,
No the Print statement, debug for the time being, is in the trigger and is
after the exec of the SO
Thanks
RObert
"ML" <ML@.discussions.microsoft.com> wrote in message
news:0E2F2CE4-C6DD-496C-8200-73F3BDDFC489@.microsoft.com...
> Does the procedure end with that PRINT statement?
>
> ML
> --
> http://milambda.blogspot.com/|||Since I can't see the entire code used in the process you're debugging, try
putting a PRINT statement at the end of each procedure - e.g. "print
'<procedure name> done."
Are there more triggers?
ML
http://milambda.blogspot.com/|||>The SP, by nature update multiple rows in child tables, as A result I would
>expect the following:
>(1 row(s) affected)
>(1 row(s) affected)
>(4 row(s) affected)
>But with the print statement I am gettingh
>(1 row(s) affected)
>(1 row(s) affected)
>Check SIP
>(4 row(s) affected)
>Should'nt the Check sip statement come last
I have the ouput of PRINT appear out of order relative to the retults
from SELECT (or UPDATE or INSERT) all the time. They apparently are
buffered independently. This is annoying, and I have never seen it
documented, but it is expected and nothing to worry about.
Roy|||HI Roy,
Thanks. THats reassuring
Robert
"Roy Harvey" <roy_harvey@.snet.net> wrote in message
news:sirpv1duj8up6ssb9as694g5fpntjhq9li@.
4ax.com...
would
> I have the ouput of PRINT appear out of order relative to the retults
> from SELECT (or UPDATE or INSERT) all the time. They apparently are
> buffered independently. This is annoying, and I have never seen it
> documented, but it is expected and nothing to worry about.
> Roy