Modifing the row that invokes a trigger from within that trigger

When a row gets modified and it invokes a trigger, we would like to be
able to update the row that was modified inside the trigger. This is
(basically) how we are doing it now:

CREATE TRIGGER trTBL ON TBL
FOR UPDATE, INSERT, DELETE
as
update TBL
set fld = 'value'
from inserted, TBL
where inserted.id= TBL.id

...

This work fine but it seems like it could be optimized. Clearly we are
having to scan the entire table again to update the row. But shouldn't
the trigger already know which row invoked it. Do we have to scan the
table again for this row or is their some syntax that allows us to
update the row that invoked the trigger. If not, why. It seems like
this would be a fairly common task. Thanks.--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1

The trigger does "know" which rows have been updated, they are in the
inserted recordset.

If you want to eliminate table scans put an index on the "joining"
columns between inserted and the updated table: in your case the [id]
column in the TBL table.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmgkHIechKqOuFEgEQKj0ACg/gThmkK3I5FVs014i96EY1KEqyEAniEQ
TeGhjpWGcoYjj51fomXBxth4
=ilXF
--END PGP SIGNATURE--

nosbtr1 wrote:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it. Do we have to scan the
> table again for this row or is their some syntax that allows us to
> update the row that invoked the trigger. If not, why. It seems like
> this would be a fairly common task. Thanks.|||On 21 Apr 2005 14:11:20 -0700, nosbtr1 wrote:

>When a row gets modified and it invokes a trigger, we would like to be
>able to update the row that was modified inside the trigger. This is
>(basically) how we are doing it now:
>CREATE TRIGGER trTBL ON TBL
>FOR UPDATE, INSERT, DELETE
>as
>update TBL
> set fld = 'value'
>from inserted, TBL
> where inserted.id= TBL.id
>...
>This work fine but it seems like it could be optimized. Clearly we are
>having to scan the entire table again to update the row. But shouldn't
>the trigger already know which row invoked it. Do we have to scan the
>table again for this row or is their some syntax that allows us to
>update the row that invoked the trigger. If not, why. It seems like
>this would be a fairly common task. Thanks.

Hi nosbtr1,

1. There is no "special syntax" to find the rowS (note the plural)
affected by the statement that fired the trigger, other than what you
are already using: the inserted and deleted pseudo-tables.

2. Your statement that this will cause a table scan is incorrect - if
you have a PRIMARY KEY constraint, a UNIQUE constraint or an INDEX
defined for the id column in the table, SQL Server can use an index seek
(followed by a bookmark lookup if the index used is nonclustered).

3. Your trigger won't do anything on delete operations. First because
the inserted pseudotable is always empty on a delete, and second becuase
the rows you are attempting to modify are already removed from the
table.

4. Why not rewrite the UPDATE statement above to the ANSI-compliant
alternative:

UPDATE Tbl
SET Fld = 'value'
WHERE EXISTS (SELECT *
FROM inserted
WHERE inserted.id = Tbl.id)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You could use an instead of trigger, in this way you actually control
the way the data is updated in the table.

the code is much more complex though|||nosbtr1 (nosbtr1@.yahoo.com) writes:
> When a row gets modified and it invokes a trigger, we would like to be
> able to update the row that was modified inside the trigger. This is
> (basically) how we are doing it now:
> CREATE TRIGGER trTBL ON TBL
> FOR UPDATE, INSERT, DELETE
> as
> update TBL
> set fld = 'value'
> from inserted, TBL
> where inserted.id= TBL.id
> ...
> This work fine but it seems like it could be optimized. Clearly we are
> having to scan the entire table again to update the row. But shouldn't
> the trigger already know which row invoked it.

A trigger fires once per statement, and thus many rows may be affected.

The main performance thing with triggers is that one should be aware of
that the inserted/deleted tables are fairly slow. If you trigger makes
frequent references to these tables, it's a good idea to insert the data
into the pseudo tables into table variables, and work with these instead.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Modifing Data in SQL

Hello, and sorry for the vauge title.

Basically, I am building a web application where people can enter in raffles. So I set up a SQL DataBase, where I have a table for each raffel going on. In each, I have a UserName collum, and a ID collum, the ID being the key collum. On my page, I basically have a TextBox and a Button.

I know I add a SqlDataSorce to the page, and use a INSERT command, however I am not sure how to parse this in a way to make it so that when you type your UserName into the TextBox, and then click the submit button, it will create a row using the UserName.

Thanks for the help!

I'm assuming that your key column is an identity column, so we don't have to create a new key.

You have a seperate table for each raffle, and each one has its own username column? I suspect that you're better off with one table for all raffles, and a key field for the raffle that a row is for. That way you don't have to create a new table every time someone does a raffle.

Let's assume that the function GetTable() returns the name of the table for the raffle the user's signing up for.

source.InsertCommand = "Insert into " + GetTable() + "(UserName) Values(@.UserName)"

Then bind the data source to your textbox.

|||

Thanks, but I don't get the last part, "Then bind the data source to your textbox." Because if you do that, then it won't interact with the database.

And if you ment it the other way around, (Bind the textbox to the data source), I can't. You can't bind textboxes to datasources.

Help?

|||

I don't do much with SqlDataSources. If it were a straight SqlCommand, then inthe click event of your button, I'd have

cmd.Parameters["@.UserName"].value = txtUserName.Text;

cmd.ExecuteNonQuery();

|||

Sorry if this is really obvious, and I'm just stupid, but I can't figure out what to use for "cmd". I think you are refering to the SqlCommand, (Insert), but that doesn't work. (I've also tried InsertCommand).

Sorry that I can't get this.

Modeling a Matrix

What is the best way to model a matrix in terms of table design? I want to
model a sociogram, which is basically a network diagram with weights
assigned to the edges. Once, established I would want to use it to do matrix
computations (matrix algebra). So, the simple version would be something
like the following:
0 1 1
1 0 1
1 1 0
The rows and columns will constitute the same set of data. In other words,
the matrix will reflect the relationship between like entities from the same
set. Relationships between persons of a given set of people, for example.
Just wondering if there is a clever way of modeling this, or if I should
just use a table with three columns: entityA, entityB, edgeValue...
Thanks
BKGet a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
in SQL.
CREATE TABLE Martix
(i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
element_value FLOAT NOT NULL,
PRIMARY KEY (i, j, k));
Then you talk about graphs in SQL with a sociogram -- which is it?|||(grabs SQL FOR SMARTIES off of his bookshelf...and there it is on p303! ..
should have checked there first)
Excellent, thanks! I thought that might be the best way to do, but just
wanted to validate my gut feeling...
The answer to your question is: both. Really its just a matrix
representation of a sociogram, so the edges will represent the relationships
between the nodes (and the elements of the matrix). The row and column
vectors will be the same and will represent the "people".
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1115680013.666216.159290@.f14g2000cwb.googlegroups.com...
> Get a copy of SQL FOR SMARTIES; there is a whole chapter on matrix math
> in SQL.
> CREATE TABLE Martix
> (i INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n1>>,
> j INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n2>>,
> k INTEGER NOT NULL CHECK (i BETWEEN 1 AND <<n3>>,
> element_value FLOAT NOT NULL,
> PRIMARY KEY (i, j, k));
> Then you talk about graphs in SQL with a sociogram -- which is it?
>|||BK,
See http://groups.google.co.uk/groups?q=BBEB95_9DBB7E for an example.
Steve Kass
Drew University
BK wrote:

>What is the best way to model a matrix in terms of table design? I want to
>model a sociogram, which is basically a network diagram with weights
>assigned to the edges. Once, established I would want to use it to do matri
x
>computations (matrix algebra). So, the simple version would be something
>like the following:
>0 1 1
>1 0 1
>1 1 0
>The rows and columns will constitute the same set of data. In other words,
>the matrix will reflect the relationship between like entities from the sam
e
>set. Relationships between persons of a given set of people, for example.
>Just wondering if there is a clever way of modeling this, or if I should
>just use a table with three columns: entityA, entityB, edgeValue...
>Thanks
>BK
>
>

Missing images in reportviewer report

Has anyone experienced intermittent missing images in a report served up with the reportviewer from a web form? Basically I've got a report that has several embedded jpg images and when I bring it up in IE 6 over half the time it will show the infamous red x stating the image was not downloaded. If I refresh the page a few times the images will eventually show but they will disappear again after another refresh or two. Interestingly enough I don't experience this behavior at all when using Firefox. I originally thought the HTTP compression I have set up for IIS 6 might be interfering, but I still have the same problem when I turn it off. I tried using Fiddler to see what was failing but for some reason it works every time when I have Fiddler running. Any help/advice would be greatly appreciated.same thing here using ie7. did you ever resolve it?|||

I too have this issue. It happens in two places for me, an external image (logo) that is uploaded through the Report Manager, and also the charts are not showing.
These problems reside when using the reportviewer.
When I view the reports through the default report manager, it all looks fine.

|||I was also not getting the print dialog box after pressing on the printer icon in report server. Moved the app to another server and the images and the dialog box started working. I believe it was some security setting that was preventing it, never found out what was causing it.|||

vatech1993:

Has anyone experienced intermittent missing images in a report served up with the reportviewer from a web form? Basically I've got a report that has several embedded jpg images and when I bring it up in IE 6 over half the time it will show the infamous red x stating the image was not downloaded. If I refresh the page a few times the images will eventually show but they will disappear again after another refresh or two. Interestingly enough I don't experience this behavior at all when using Firefox. I originally thought the HTTP compression I have set up for IIS 6 might be interfering, but I still have the same problem when I turn it off. I tried using Fiddler to see what was failing but for some reason it works every time when I have Fiddler running. Any help/advice would be greatly appreciated.

Any solution?

|||

As far as e

|||

As far as e