Row wise operation in MSSQL Server

How can I create a row wise trigger in MSSQL Server .

CREATE TRIGGER trigName ON tableName for
INSERT , UPDATE , DELETE
AS ...

For a multiple delete , I got only one trigger invocation .
But I need individual trigger calls for each row ...
How can I do this in t-sql ?
Is there any usage like FOR EACH ROW in Oracle ?
Is it possible through INSTEAD OF TRIGGER ?
Please help !!!!!!!!Yes, you could put a cursor in the trigger that operates on each row of the INSERTED table. But do yourself a favor and don't do it. Find a set-based solution using the INSERTED table instead.

Please describe what you are trying to do and why you think you need a row-wise operator (cursor). These are generally only needed by dynamic sql procedures or processes where the results of the operation on one record affects the results of the operation on the next record.|||AND...I'll add...anything that would require a cursor should not be done in a trigger...sql

Row retrieval problem in small table

Dear Participant,

I face following problem by last few day, please help me for the same

My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.

I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.

Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.

I dont understand what is the problem.

Thanks

R.MallSounds like a blocking problem to me. The next time this happens, take a look in Enterprise Manager Management->Current Activity->Process Info, and see if people are getting blocked in general. After that, you can try to narrow down the table, but I think you already have that table.|||I will see it, but I had look over the same matter on server itself, without a single user in LAN.|||Is there any jobs running in parallel... Jobs by default is a transaction... u can see once a transaction is running and if u try to get data thru enterprise manage and trough that try to open table it will not display any... but select should run in that case.....

I got a doubt what does this blocking means? is that 'lock' u guys are mentioning... If its lock u can run the below query to check if there is any lock still running,

SELECT spid, cmd, status, loginame, open_tran,
datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)|||Is there any easy going methods to resolve this problem.

Thanks

R.Mall|||Use sql profiler to watch it.|||i suggest you use SP_WHO to check the instances running and status of each instance. you would be able to see also if there are blocking...

use the Profiler if you want to know the different SQL commands being processed by the server. However, use this with caution since it might cause your system to slow down thus giving you the false impression that your script is slow.

row limit in MSSQL

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?There is no way to overcome this limit in SQL 2000 other than to split the one table into multiple tables (as it seems you have already done).

Another workaround might be to take some of your longer varchar fields and convert them into text; however, you will lose some functionality (searching and indexing) if you choose this option.

Regards,

hmscott

There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP|||Actually, the 8060 byte limit is for storing rows in a table. Result set rows can be many times larger than 8060 bytes.

-PatP
I seems to be different for me. I have row size in table roughly 5k but I need to connect more tables. Than (during SELECT command) this error comes.|||Does someone know if this will be fixed in MS SQL 2005 ?|||Try this one on for size, it generates lots of 16 Kb+ rows in the result set.
CREATE TABLE Sladky0 (
pk INT IDENTITY PRIMARY KEY (pk)
, junque00 CHAR(255) NOT NULL DEFAULT '00'
, junque01 CHAR(255) NOT NULL DEFAULT '01'
, junque02 CHAR(255) NOT NULL DEFAULT '02'
, junque03 CHAR(255) NOT NULL DEFAULT '03'
, junque04 CHAR(255) NOT NULL DEFAULT '04'
, junque05 CHAR(255) NOT NULL DEFAULT '05'
, junque06 CHAR(255) NOT NULL DEFAULT '06'
, junque07 CHAR(255) NOT NULL DEFAULT '07'
, junque08 CHAR(255) NOT NULL DEFAULT '08'
, junque09 CHAR(255) NOT NULL DEFAULT '09'
, junque0a CHAR(255) NOT NULL DEFAULT '0a'
, junque0b CHAR(255) NOT NULL DEFAULT '0b'
, junque0c CHAR(255) NOT NULL DEFAULT '0c'
, junque0d CHAR(255) NOT NULL DEFAULT '0d'
, junque0e CHAR(255) NOT NULL DEFAULT '0e'
, junque0f CHAR(255) NOT NULL DEFAULT '0f'
)

CREATE TABLE Sladky1 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque10 CHAR(255) NOT NULL DEFAULT '10'
, junque11 CHAR(255) NOT NULL DEFAULT '11'
, junque12 CHAR(255) NOT NULL DEFAULT '12'
, junque13 CHAR(255) NOT NULL DEFAULT '13'
, junque14 CHAR(255) NOT NULL DEFAULT '14'
, junque15 CHAR(255) NOT NULL DEFAULT '15'
, junque16 CHAR(255) NOT NULL DEFAULT '16'
, junque17 CHAR(255) NOT NULL DEFAULT '17'
, junque18 CHAR(255) NOT NULL DEFAULT '18'
, junque19 CHAR(255) NOT NULL DEFAULT '19'
, junque1a CHAR(255) NOT NULL DEFAULT '1a'
, junque1b CHAR(255) NOT NULL DEFAULT '1b'
, junque1c CHAR(255) NOT NULL DEFAULT '1c'
, junque1d CHAR(255) NOT NULL DEFAULT '1d'
, junque1e CHAR(255) NOT NULL DEFAULT '1e'
, junque1f CHAR(255) NOT NULL DEFAULT '1f'
)

CREATE TABLE Sladky2 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque20 CHAR(255) NOT NULL DEFAULT '20'
, junque21 CHAR(255) NOT NULL DEFAULT '21'
, junque22 CHAR(255) NOT NULL DEFAULT '22'
, junque23 CHAR(255) NOT NULL DEFAULT '23'
, junque24 CHAR(255) NOT NULL DEFAULT '24'
, junque25 CHAR(255) NOT NULL DEFAULT '25'
, junque26 CHAR(255) NOT NULL DEFAULT '26'
, junque27 CHAR(255) NOT NULL DEFAULT '27'
, junque28 CHAR(255) NOT NULL DEFAULT '28'
, junque29 CHAR(255) NOT NULL DEFAULT '29'
, junque2a CHAR(255) NOT NULL DEFAULT '2a'
, junque2b CHAR(255) NOT NULL DEFAULT '2b'
, junque2c CHAR(255) NOT NULL DEFAULT '2c'
, junque2d CHAR(255) NOT NULL DEFAULT '2d'
, junque2e CHAR(255) NOT NULL DEFAULT '2e'
, junque2f CHAR(255) NOT NULL DEFAULT '2f'
)

CREATE TABLE Sladky3 (
pk INT NOT NULL FOREIGN KEY (pk) REFERENCES Sladky0 (pk)
, junque30 CHAR(255) NOT NULL DEFAULT '30'
, junque31 CHAR(255) NOT NULL DEFAULT '31'
, junque32 CHAR(255) NOT NULL DEFAULT '32'
, junque33 CHAR(255) NOT NULL DEFAULT '33'
, junque34 CHAR(255) NOT NULL DEFAULT '34'
, junque35 CHAR(255) NOT NULL DEFAULT '35'
, junque36 CHAR(255) NOT NULL DEFAULT '36'
, junque37 CHAR(255) NOT NULL DEFAULT '37'
, junque38 CHAR(255) NOT NULL DEFAULT '38'
, junque39 CHAR(255) NOT NULL DEFAULT '39'
, junque3a CHAR(255) NOT NULL DEFAULT '3a'
, junque3b CHAR(255) NOT NULL DEFAULT '3b'
, junque3c CHAR(255) NOT NULL DEFAULT '3c'
, junque3d CHAR(255) NOT NULL DEFAULT '3d'
, junque3e CHAR(255) NOT NULL DEFAULT '3e'
, junque3f CHAR(255) NOT NULL DEFAULT '3f'
)

DECLARE
@.loop INT
, @.pk INT

SET @.loop = 10

WHILE 0 < @.loop
BEGIN
INSERT INTO Sladky0 (junque00) VALUES (DEFAULT)
SET @.pk = @.@.identity
INSERT INTO Sladky1 (pk) VALUES (@.pk)
INSERT INTO Sladky2 (pk) VALUES (@.pk)
INSERT INTO Sladky3 (pk) VALUES (@.pk)
SELECT @.loop = @.loop - 1
END

SELECT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)-PatP|||ok , thanks|||I had a bit different SQL command in my application. I have join done over where condition and used DISTINCT in command.
But I found if I use DISTICNT keyword before that this error comes even with your offered SQL command with JOINs. So it seems to me different with DISTINCT or not - but why? So I cannot use keyword distinct for bigger result over tables - I guess.

(
SELECT DISTINCT *
FROM Sladky0
JOIN Sladky1 ON (Sladky1.pk = Sladky0.pk)
JOIN Sladky2 ON (Sladky2.pk = Sladky0.pk)
JOIN Sladky3 ON (Sladky3.pk = Sladky0.pk)
) doesn't work|||Avoid using DISTINCT this way (it implicity creates a table), use a GROUP BY instead.

-PatP|||Does it mean that it is better to mention all columns in GROUP BY statement than use DISCTINCT expression? So I have very long statements. To mention only some of them is not possible I think.|||Listing every returned column in a GROUP BY is the only was I know to avoid creating the work table that is created by DISTINCT.

-PatP|||I have tryied it - it really works fine. Thanks|||I know that it is something of a pain to have to deal with the problem by changing your code, but the designers made some trade off decisions in the query engine that work very well for 99.9% of the queries, but clobber queries like yours. It is annoying to have your code be the "lucky" one that needs to be changed, but at least the change is better than waiting for another database upgrade (at least in most cases)!

-PatP|||I agree with you. I am glad that I have solution for now for MSSQL server.
In our application I try to have as much as possible common code for MySQL (ODBC, native), MSSQL (ODBC), ORACLE(ODBC, native),... but you can imagine a lot of different parts.|||Yukon allows rows to span across multiple pages.

Row Level Locking

I have a busy transactional table , I wanna use row level locking mechanism in msSQL.

SELECT * FROM PARTYWITH (UPDLOCK ROWLOCK)

where LastName ='Clinton'

is there anydownsides of this approach?

Here's a pretty good article talking about the pro's and con's

http://www.sql-server-performance.com/lock_contention_tamed_article.asp

From what I can tell, the only disadvantage, isnt specific to ROWLOCK itself, just locking in general in busy tables.

Round funtion on entire columns in MSSQL?

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steveOn 5 May 2004 02:40:15 -0700, steve wrote:

>Hi,
>I'd like to round all amounts in a certain column to 2 decimals.
>I tried the following query, but eventhough the syntax is correct, it
>doesn't give any result:
>update gbkmut
>set bdr_hfl = round(bdr_hfl,2)
>can anyone help me?
>cheers,
>steve

Hi Steve,

What do you mean with "doesn't give any result"?

If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.

If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||stefan.van.den.steen@.exact.be (steve) wrote in message news:<79d8a1b0.0405050140.6f442694@.posting.google.com>...
> Hi,
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)
> can anyone help me?
> cheers,
> steve

At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?

Simon|||steve (stefan.van.den.steen@.exact.be) writes:
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)

There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:

select convert(float, 1.89)

In Query Analyzer this displays as 1.8899999999999999.

In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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