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

Question about Parameter

Hi,
I need to design a Sales Order (SO) report for my company. In the SO
report, I need to print out the company name, company address, Telephone no.
... etc in report header. For this design, it serve 5 sub-company under my
company group. Therefore, I store the company data in a table - name
CompanyInfo. Everytime I need to select correct company name to print it out.
But I know that it cannot print "Field" in report header in RS. I try to
put it into parameter and print it out. It OK but all of the parameter will
be show to user. For this point, I don't want our user to see such
information.
Any idea for me?
Thank You!
TonyYou can have multiple datasets in a report. If you put a list at the top of
the report and your query has only one record, then the data will look for
all intents and purposes as the report header. Then following that have your
table or matrix or whatever. I do this all the time and it looks exactly the
same.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> Hi,
> I need to design a Sales Order (SO) report for my company. In the SO
> report, I need to print out the company name, company address, Telephone
> no.
> ... etc in report header. For this design, it serve 5 sub-company under
> my
> company group. Therefore, I store the company data in a table - name
> CompanyInfo. Everytime I need to select correct company name to print it
> out.
> But I know that it cannot print "Field" in report header in RS. I try to
> put it into parameter and print it out. It OK but all of the parameter
> will
> be show to user. For this point, I don't want our user to see such
> information.
> Any idea for me?
> Thank You!
> Tony
>|||Hi Bruce,
Thank you for your reply!
I already try this way and it look OK. But all parameters will be shown at
the top of the report! It looks no good! Can I hidden the parameter? It is
because there are 6 parameters for such report header.
Thank You!
Tony
"Bruce L-C [MVP]" wrote:
> You can have multiple datasets in a report. If you put a list at the top of
> the report and your query has only one record, then the data will look for
> all intents and purposes as the report header. Then following that have your
> table or matrix or whatever. I do this all the time and it looks exactly the
> same.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> > Hi,
> >
> > I need to design a Sales Order (SO) report for my company. In the SO
> > report, I need to print out the company name, company address, Telephone
> > no.
> > ... etc in report header. For this design, it serve 5 sub-company under
> > my
> > company group. Therefore, I store the company data in a table - name
> > CompanyInfo. Everytime I need to select correct company name to print it
> > out.
> >
> > But I know that it cannot print "Field" in report header in RS. I try to
> > put it into parameter and print it out. It OK but all of the parameter
> > will
> > be show to user. For this point, I don't want our user to see such
> > information.
> >
> > Any idea for me?
> > Thank You!
> >
> > Tony
> >
> >
>
>|||You can have hidden parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Tony" <Tony@.discussions.microsoft.com> wrote in message
news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> Hi Bruce,
> Thank you for your reply!
> I already try this way and it look OK. But all parameters will be shown
> at
> the top of the report! It looks no good! Can I hidden the parameter? It
> is
> because there are 6 parameters for such report header.
> Thank You!
> Tony
>
> "Bruce L-C [MVP]" wrote:
>> You can have multiple datasets in a report. If you put a list at the top
>> of
>> the report and your query has only one record, then the data will look
>> for
>> all intents and purposes as the report header. Then following that have
>> your
>> table or matrix or whatever. I do this all the time and it looks exactly
>> the
>> same.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Tony" <Tony@.discussions.microsoft.com> wrote in message
>> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
>> > Hi,
>> >
>> > I need to design a Sales Order (SO) report for my company. In the SO
>> > report, I need to print out the company name, company address,
>> > Telephone
>> > no.
>> > ... etc in report header. For this design, it serve 5 sub-company
>> > under
>> > my
>> > company group. Therefore, I store the company data in a table - name
>> > CompanyInfo. Everytime I need to select correct company name to print
>> > it
>> > out.
>> >
>> > But I know that it cannot print "Field" in report header in RS. I try
>> > to
>> > put it into parameter and print it out. It OK but all of the parameter
>> > will
>> > be show to user. For this point, I don't want our user to see such
>> > information.
>> >
>> > Any idea for me?
>> > Thank You!
>> >
>> > Tony
>> >
>> >
>>|||Thank You! I try it!
"Bruce L-C [MVP]" wrote:
> You can have hidden parameters.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> news:7FABFC55-8149-4484-9C80-AD70FC318BCF@.microsoft.com...
> > Hi Bruce,
> >
> > Thank you for your reply!
> >
> > I already try this way and it look OK. But all parameters will be shown
> > at
> > the top of the report! It looks no good! Can I hidden the parameter? It
> > is
> > because there are 6 parameters for such report header.
> >
> > Thank You!
> > Tony
> >
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> You can have multiple datasets in a report. If you put a list at the top
> >> of
> >> the report and your query has only one record, then the data will look
> >> for
> >> all intents and purposes as the report header. Then following that have
> >> your
> >> table or matrix or whatever. I do this all the time and it looks exactly
> >> the
> >> same.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Tony" <Tony@.discussions.microsoft.com> wrote in message
> >> news:7DCDBD8B-603D-44EF-B9C2-F409683657F4@.microsoft.com...
> >> > Hi,
> >> >
> >> > I need to design a Sales Order (SO) report for my company. In the SO
> >> > report, I need to print out the company name, company address,
> >> > Telephone
> >> > no.
> >> > ... etc in report header. For this design, it serve 5 sub-company
> >> > under
> >> > my
> >> > company group. Therefore, I store the company data in a table - name
> >> > CompanyInfo. Everytime I need to select correct company name to print
> >> > it
> >> > out.
> >> >
> >> > But I know that it cannot print "Field" in report header in RS. I try
> >> > to
> >> > put it into parameter and print it out. It OK but all of the parameter
> >> > will
> >> > be show to user. For this point, I don't want our user to see such
> >> > information.
> >> >
> >> > Any idea for me?
> >> > Thank You!
> >> >
> >> > Tony
> >> >
> >> >
> >>
> >>
> >>
>
>

question about order in which queries fire in trigger

I ran into this trigger in a project. Is this tigger firing Delete from
webproducts join Deleted... first? and then Delete from webproducts join
Inserted... second? Or is the ...join Inserted firing first and then the
...deleted?
The table SubDetail gets updated on some column, any column. No deletes
involved. What would constitute deleted if it isn't Delete from webproducts
join Inserted?
----
CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
for update
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
return
---
Thanks,
RichOn Fri, 12 May 2006 15:17:01 -0700, Rich wrote:

>I ran into this trigger in a project. Is this tigger firing Delete from
>webproducts join Deleted... first? and then Delete from webproducts join
>Inserted... second? Or is the ...join Inserted firing first and then the
>...deleted?
>The table SubDetail gets updated on some column, any column. No deletes
>involved. What would constitute deleted if it isn't Delete from webproduct
s
>join Inserted?
(snip code)
Hi Rich,
I'm not sure if I understand all your questions.
However:
1. Statements in a trigger are executed sequentially, top-down (unless
you explicitly change order of execution with GO TO, WHILE, IF or other
control-flow statements).
2. In an UPDATE trigger, the deleted pseudo-table holds the before
update image of all affected rows and the inserted pseudo-table holds
the after update image of all affected rows.
Hugo Kornelis, SQL Server MVP|||Rich (Rich@.discussions.microsoft.com) writes:
> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts
> join Inserted... second?
Yes, statements are executed sequentially.

> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from >
> webproducts join Inserted?
In an UPDATE trigger, both the "inserted" and "deleted" tables are
popoulated. "inserted" holds the after-image data, and "deleted" is
before-image. That is how the row looked like before the update.

> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
Seems funny to run two deletes like that...
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|||Thank you all for your replies. Actually, I left out other stuff from that
trigger which I did understand. However, the explanations provided here did
enlighten me about one thing that I was not aware of, the image of the table
before the action - "Deleted".
I actually do need to capture the before image, and I had no idea how to
describe that. Now I understand. Thank you all for explaining this to me.
Now I understand.
It doesn't solve my immediate problem, but now I think I can figure it out.
Incase anyone cares, my problem is that I am updating this table from a
client app using com ADO and want to retrieve the records affected count. I
f
...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
counts of records affected. But if ...deleted... comes before ...inserted..
.
then
cmd.Execute j, , adExecuteNoRecords
is not returning the count of records affected. But at least now I can
tweak the trigger a little since I have a better idea what is going on.
Thanks again all,
Rich
"Rich" wrote:

> I ran into this trigger in a project. Is this tigger firing Delete from
> webproducts join Deleted... first? and then Delete from webproducts join
> Inserted... second? Or is the ...join Inserted firing first and then th
e
> ...deleted?
> The table SubDetail gets updated on some column, any column. No deletes
> involved. What would constitute deleted if it isn't Delete from webproduc
ts
> join Inserted?
> ----
--
> CREATE trigger t_For_Update_WebProducts on dbo.SubDetail
> for update
> delete WebProducts
> from WebProducts wp
> inner join Deleted d
> on (d.RecordID = wp.RecordID )
> delete WebProducts
> from WebProducts wp
> inner join Inserted i
> on (i.RecordID = wp.RecordID)
> return
> ---
> Thanks,
> Rich|||Let me clarify a few things here.. Rich.. if you don't mind.
Take your case
delete WebProducts
from WebProducts wp
inner join Deleted d
on (d.RecordID = wp.RecordID )
delete WebProducts
from WebProducts wp
inner join Inserted i
on (i.RecordID = wp.RecordID)
Here first you are joining with deleted table on recordID and then the same
with inserted. And as Hugo and Erland have mentioned, Deleted has the old
image and Inserted has the new image. So this delete with join from deleted
is actually redundant.
There are two cases. One where the update is happening on the column
"RecordID".
In that case you will not be having any row in the table with the value of
"RecordID" that you have in deleted. So the first delete will not delete any
rows (Though I made an assumption here, that RecordID is the PKey :)
And if RecordID is not being updated, then the first delete will delete the
rows and the second delete will try to delete the same rows which will not
happen, so you will be getting rowcount = 0.
What I don't understand is your requirement. Do you want to delete the
updated rows from the table'|||On Fri, 12 May 2006 20:23:01 -0700, Omnibuzz wrote:
(snip)
> So this delete with join from deleted
>is actually redundant.
Hi Omnibuzz,
Good catch!

>What I don't understand is your requirement. Do you want to delete the
>updated rows from the table'
Same here. I hope that Rich will return and explain his requirements
some more.
Hugo Kornelis, SQL Server MVP|||examnotes <Rich@.discussions.microsoft.com> writes:
>Thank you all for your replies. Actually, I left out other stuff from that
>trigger which I did understand. However, the explanations provided here di
d
>enlighten me about one thing that I was not aware of, the image of the tabl
e
>before the action - "Deleted".
>I actually do need to capture the before image, and I had no idea how to
>describe that. Now I understand. Thank you all for explaining this to me.
>Now I understand.
>It doesn't solve my immediate problem, but now I think I can figure it out.
>Incase anyone cares, my problem is that I am updating this table from a
>client app using com ADO and want to retrieve the records affected count.
If
>...deleted... comes after ...inserted... in the trigger, I can retrieve th
e
>counts of records affected. But if ...deleted... comes before ...inserted.
.
>then
>cmd.Execute j, , adExecuteNoRecords
> is not returning the count of records affected. But at least now I can
>tweak the trigger a little since I have a better idea what is going on.
I think you need to skip adExecuteNoRecords and instead loop over
:NextRecordset. Each UPDATE and DELETE causes a rowcount. Obviously,
you want the first: the number of rows inserted. You must loop over
.NextRecordset to get all rowcounts, even if you don't need them.
--
Erland Sommarskog, Stockholm, esquel@.sommarskog.se

question about designing tables

Hi,

I want to define a table that has "order details" about one order where I can store a variable length list of prodcuts ordered in one order:

[prodID][qty][price]

That is to attch a collection of products to one order entry.

Thank you,
shlomi711Then you should create a table that looks like this:

(RowID Integer Identity) - Not really necessary, but I like to have unique row number
OrderID Integer - The order number (to which the details are connected)
prodID Integer - The product ID
qty money - the amount of products
price money - the price of the products.

Regards
Fredrik