raiserror

Hi
I want to make a trigger that prevent users from deleting rows that they
didnt insert and make en raiseror that tells them why the couldnt remove the
row, the table contains a column that says what sql login made the insert'
does anyone have an idea?you can do this in two ways.
1. write a query such that u delete only the rows the user inserted:
DELETE FROM <TABLE> WHERE user_ID = user
or
2.
In the trigger, check the deleted table
IF NOT EXISTS(SELECT * FROM DELETED where user_id = user)
please let me know if u have any questions
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"LeSurfer" wrote:

> Hi
> I want to make a trigger that prevent users from deleting rows that they
> didnt insert and make en raiseror that tells them why the couldnt remove t
he
> row, the table contains a column that says what sql login made the insert?
?
> does anyone have an idea?|||Hi
I don′t want the trigger to delete any rows, i want the trigger to prevent
users from deleteing rows which they ditn′t insert.
I have a program with a table with customers, when a new customer is created
it also inserts a column with the sql user login id name of the person how
made the insert. Now i want to make a trigger, so that you can only remove
rows which you inserted!
Fredrik
"Chandra" wrote:
> you can do this in two ways.
> 1. write a query such that u delete only the rows the user inserted:
> DELETE FROM <TABLE> WHERE user_ID = user
> or
> 2.
> In the trigger, check the deleted table
> IF NOT EXISTS(SELECT * FROM DELETED where user_id = user)
> please let me know if u have any questions
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "LeSurfer" wrote:
>|||WHERE user_ID = user is the key as Chandra stated.
You can only delete the rows that match the currently logged in user.
Whatever logic you are using to delete the row in the trigger then add the
user_ID = user condition. Therefore if dbo logs in and tries to delete rows
'LeSurfer' entered he will not be able coz it wont meet the condition.
Your raiserror can be scripted as follows:
GOTO ERRORPOINT
ERROR_POINT:
raiserror(@.text,16,-1)
ROLLBACK TRANSACTION
GOTO FINISH
FINISH:
End
"LeSurfer" wrote:
> Hi
> I don′t want the trigger to delete any rows, i want the trigger to preven
t
> users from deleteing rows which they ditn′t insert.
> I have a program with a table with customers, when a new customer is creat
ed
> it also inserts a column with the sql user login id name of the person how
> made the insert. Now i want to make a trigger, so that you can only remove
> rows which you inserted!
> Fredrik
> "Chandra" wrote:
>|||On Tue, 6 Sep 2005 03:25:02 -0700, LeSurfer wrote:

>Hi
>I want to make a trigger that prevent users from deleting rows that they
>didnt insert and make en raiseror that tells them why the couldnt remove th
e
>row, the table contains a column that says what sql login made the insert'
>does anyone have an idea?
Hi LeSurfer,
Something like this, maybe?
CREATE TRIGGER MyTrigger
ON MyTable FOR DELETE
AS
IF EXISTS (SELECT *
FROM deleted
WHERE TheColumnWithTheUserID <> CURRENT_USER)
BEGIN
RAISERROR ('You can''t delete rows that were inserted by someone
else', 16, 1)
ROLLBACK TRANSACTION
END
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Perfekt, thanks a lot.........
"Hugo Kornelis" wrote:

> On Tue, 6 Sep 2005 03:25:02 -0700, LeSurfer wrote:
>
> Hi LeSurfer,
> Something like this, maybe?
> CREATE TRIGGER MyTrigger
> ON MyTable FOR DELETE
> AS
> IF EXISTS (SELECT *
> FROM deleted
> WHERE TheColumnWithTheUserID <> CURRENT_USER)
> BEGIN
> RAISERROR ('You can''t delete rows that were inserted by someone
> else', 16, 1)
> ROLLBACK TRANSACTION
> END
> go
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

RaiseError not getting what you what!!

Not PARSING "..." is this a BUG!!!!!

[ BEGIN
RAISERROR ('The employee is already in the list of overtime for this
date!..............................', 16,1)
RETURN -1
END
] = [ BEGIN
RAISERROR ('The employee is already in the list of overtime for this
date!', 16,1)
RETURN -1
END
]

============The script you posed is not a valid Transact-SQL. It is unclear to me what
you are trying to accomplish but below is an example.

CREATE PROC CheckOvertime
@.EmployeeId int,
@.OvertimeDate smalldatetime
AS
IF EXISTS
(
SELECT *
FROM OverTime
WHERE EmployeeID = @.EmployeeId AND
OvertimeDate = @.OvertimeDate
)
BEGIN
RAISERROR ('The employee is already in the list of overtime for this>
date!..............................', 16,1)
RETURN -1
END
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

<squirrelv5@.yahoo.com> wrote in message
news:1102408111.648976.196080@.z14g2000cwz.googlegr oups.com...
> Not PARSING "..." is this a BUG!!!!!
> [ BEGIN
> RAISERROR ('The employee is already in the list of overtime for this
> date!..............................', 16,1)
> RETURN -1
> END
> ] = [ BEGIN
> RAISERROR ('The employee is already in the list of overtime for this
> date!', 16,1)
> RETURN -1
> END
> ]
> ============

RAISEERROR from CLR stored procedures

I'm writing:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("RAISERROR ( '" + e.Message + "', 11, 1)"));

to execute a RAISEERROR from a CLR stored procedure.

It works, but in the SqlError list I get in the client, there are two more errors that say something like '{System.Data.SqlClient.SqlError: A .NET Framework error occurred during execution of user defined routine or aggregate 'Customer__Update':
System.Data.SqlClient.SqlException: noupdate ...' ...

Is there any way to just get the message I'm sending?

Thanks

The issue here is that by ExecuteAndSend a command which executes a RAISERROR, your RAISERROR will be called in the SQL layer, and an error will be raised to the client (which is good and is what you see as the first error). However, this error will be caught by the CLR when ExecuteAndSend returns., and it will be seen as a unhandled error and wrapped by SQL when your method returns as error 6522 (CLR Error).
The way to handle this is to have your Pipe.ExecuteAndSend in a try block and then have an empty catch block which will eat the error coming back from SQL:

try { p.ExecuteAndSend(cmd); } catch { }

Hope this helps!
Niels

|||Thanks Neils.

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

However, with or without the try/catch, I got a 50000 error, not the 6522.

I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000|||

aaguiar wrote:

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

Hmm, I don't see that here, I only get one message. However if I execute the CLR proc from inside a T-SQL proc I get all kind of weird errors - that's a bug in this build, and will be fixed. Can you post your code, both server side as well as client side?

aaguiar wrote:

However, with or without the try/catch, I got a 50000 error, not the 6522.
I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

Sure, but when you do ExecuteAndSend (without the dummy try/catch), I'm willing to bet your second error is 6522. 50000 is expected as that is "user defined error".
Niels|||

I tried this but the error is not caught by a begin try/end try ... begin catch/end catch block in transact sql code. Is there a way to send a custom error which can be caught in TSQL code?

|||I am having this problem too. The RAISERROR call from my CLR Routine is not being caught in the TRY/CATCH T-SQL block. Can anyone tell me why?sql

RAISEERROR from CLR stored procedures

I'm writing:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("RAISERROR ( '" + e.Message + "', 11, 1)"));

to execute a RAISEERROR from a CLR stored procedure.

It works, but in the SqlError list I get in the client, there are two more errors that say something like '{System.Data.SqlClient.SqlError: A .NET Framework error occurred during execution of user defined routine or aggregate 'Customer__Update':
System.Data.SqlClient.SqlException: noupdate ...' ...

Is there any way to just get the message I'm sending?

Thanks

The issue here is that by ExecuteAndSend a command which executes a RAISERROR, your RAISERROR will be called in the SQL layer, and an error will be raised to the client (which is good and is what you see as the first error). However, this error will be caught by the CLR when ExecuteAndSend returns., and it will be seen as a unhandled error and wrapped by SQL when your method returns as error 6522 (CLR Error).
The way to handle this is to have your Pipe.ExecuteAndSend in a try block and then have an empty catch block which will eat the error coming back from SQL:

try { p.ExecuteAndSend(cmd); } catch { }

Hope this helps!
Niels

|||Thanks Neils.

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

However, with or without the try/catch, I got a 50000 error, not the 6522.

I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000|||

aaguiar wrote:

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

Hmm, I don't see that here, I only get one message. However if I execute the CLR proc from inside a T-SQL proc I get all kind of weird errors - that's a bug in this build, and will be fixed. Can you post your code, both server side as well as client side?

aaguiar wrote:

However, with or without the try/catch, I got a 50000 error, not the 6522.
I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

Sure, but when you do ExecuteAndSend (without the dummy try/catch), I'm willing to bet your second error is 6522. 50000 is expected as that is "user defined error".
Niels|||

I tried this but the error is not caught by a begin try/end try ... begin catch/end catch block in transact sql code. Is there a way to send a custom error which can be caught in TSQL code?

|||I am having this problem too. The RAISERROR call from my CLR Routine is not being caught in the TRY/CATCH T-SQL block. Can anyone tell me why?

RAISEERROR from CLR stored procedures

I'm writing:

SqlContext.Pipe.ExecuteAndSend(new SqlCommand("RAISERROR ( '" + e.Message + "', 11, 1)"));

to execute a RAISEERROR from a CLR stored procedure.

It works, but in the SqlError list I get in the client, there are two more errors that say something like '{System.Data.SqlClient.SqlError: A .NET Framework error occurred during execution of user defined routine or aggregate 'Customer__Update':
System.Data.SqlClient.SqlException: noupdate ...' ...

Is there any way to just get the message I'm sending?

Thanks

The issue here is that by ExecuteAndSend a command which executes a RAISERROR, your RAISERROR will be called in the SQL layer, and an error will be raised to the client (which is good and is what you see as the first error). However, this error will be caught by the CLR when ExecuteAndSend returns., and it will be seen as a unhandled error and wrapped by SQL when your method returns as error 6522 (CLR Error).
The way to handle this is to have your Pipe.ExecuteAndSend in a try block and then have an empty catch block which will eat the error coming back from SQL:

try { p.ExecuteAndSend(cmd); } catch { }

Hope this helps!
Niels

|||Thanks Neils.

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

However, with or without the try/catch, I got a 50000 error, not the 6522.

I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

|||

aaguiar wrote:

It got better, now I get to SqlErrors in the Errors collection one with my message and another saying 'The statement has been terminated.'

Hmm, I don't see that here, I only get one message. However if I execute the CLR proc from inside a T-SQL proc I get all kind of weird errors - that's a bug in this build, and will be fixed. Can you post your code, both server side as well as client side?

aaguiar wrote:

However, with or without the try/catch, I got a 50000 error, not the 6522.
I got the 6522 when I created the SqlCommand and executed it directly. If I execute using the SqlPipe.ExecuteAndSend, I got 50000

Sure, but when you do ExecuteAndSend (without the dummy try/catch), I'm willing to bet your second error is 6522. 50000 is expected as that is "user defined error".
Niels|||

I tried this but the error is not caught by a begin try/end try ... begin catch/end catch block in transact sql code. Is there a way to send a custom error which can be caught in TSQL code?

|||I am having this problem too. The RAISERROR call from my CLR Routine is not being caught in the TRY/CATCH T-SQL block. Can anyone tell me why?

raiseerror does not raise exception

Hi friends

i've a stored proc (sql 2005) that'll raiseerror statement when something violated.

but my C# application that calls this stored proc does not any throw exception when this happens !!

i remember visual basic used to through an exception for this type of things.

is it different in C# and how do we handle this scenario ?

Thanks for ur ideas.

can you show us the SP code?

moving thread to the SQL Forums

|||am doing something like below

BEGIN TRY
BEGIN TRAN

/* one of below statements may result in error*/
insert into mytable1 values (...blah..)
insert into mytable1 values (...blah..)

COMMIT
END TRY
BEGIN CATCH
DECLARE

@.ErrorMessage VARCHAR(4000)
SELECT @.ErrorMessage = 'Message: '+ ERROR_MESSAGE();

raiserror (@.ErrorMessage)
END CATCH;

i used executescalar ,executereader (in C#) but none of them throw any exception but i could access data CATCH returns though|||

Hi prk,

You'll need to specify the severity and state during the RAISERROR call:

raiserror (@.ErrorMessage,16,1)

Cheers,

Rob

|||

Thanks Rob

will give that a try

raise message from Update Trigger from External App

Hello,
I can raise this message from an Update Trigger in Query Analyzer when I
update the recordID field of my table:
RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
Is it possible to raise this message from an external app? How is this
achieved? Actually, I am sure this is possible because I remember doing it.
I just can't remember what I did because I did not document it.
Thanks,
RichWhat is this "external app"? An application that you write yourself? A TSQL
error (which is what you
raise using RAISERROR) is returned to the client application. The client app
lications is connected
to the database using an API, like ADO.NET. And, sure, you can have your dat
abase application
connect to SQL Server and issue a RAISERROR command, and have that error mes
sage be returned to the
same app, but that sounds a bit ... meaningless. If you give us more informa
tion, we can probably
give some suggestion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
> Hello,
> I can raise this message from an Update Trigger in Query Analyzer when I
> update the recordID field of my table:
> RAISERROR ('this is a test message from SubDetail Update 7', 16, 10)
> Is it possible to raise this message from an external app? How is this
> achieved? Actually, I am sure this is possible because I remember doing i
t.
> I just can't remember what I did because I did not document it.
> Thanks,
> Rich|||The external app in this case is an Access ADP. I had to modify a trigger a
few months ago, and I added a raiseerror message at the end to see my result
s
in QA - not error results - just checking what parameter was being used. I
accidentally left the raiseerror message in the trigger, and then I got a
call from an End User stating that this message was coming up all of a sudde
n
when she made updates to the table.
I found the table and reactivated the raiseerror message and I get it when I
updaet a field. The only thing I noticed is that the field I update in this
table (the master table) is not a key field. In the Detail table when I
update the RecordID field this action does not raise the message like in the
Master table. I guess my question is if this is something fundamental that
I
am missing or is it something that I need to dig around to see what is going
on?
"Tibor Karaszi" wrote:

> What is this "external app"? An application that you write yourself? A TSQ
L error (which is what you
> raise using RAISERROR) is returned to the client application. The client a
pplications is connected
> to the database using an API, like ADO.NET. And, sure, you can have your d
atabase application
> connect to SQL Server and issue a RAISERROR command, and have that error m
essage be returned to the
> same app, but that sounds a bit ... meaningless. If you give us more infor
mation, we can probably
> give some suggestion.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:54A80BEF-6285-4F6A-8B60-F06007C1BDEF@.microsoft.com...
>|||Well, I was able to raise that message if I physically update the RecordID -
meaning I go to the live table in the Access ADP which is the same thing tha
t
was going on with the Master table - where the End user was physically
writing to the table through a form. But if I update the table
programmatically from the ADP, then the message does not come up.
While I am at it, I want to alter/replace my raiseerror message. I used the
sp_addmessage sp. Since my message already exists as 50001, I don't want to
add another message. I want to alter this one. But I get an error message
in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
don't know the syntax for this. I have tried variations such as:
USE master
EXEC sp_addmessage 50001, 16,
select replace('This is a test custome message', 'custome', 'custom')
and placing REplace in other locations with no success. Any suggestions how
to do this correctly would be greatly appreciated.
"Rich" wrote:
> The external app in this case is an Access ADP. I had to modify a trigger
a
> few months ago, and I added a raiseerror message at the end to see my resu
lts
> in QA - not error results - just checking what parameter was being used.
I
> accidentally left the raiseerror message in the trigger, and then I got a
> call from an End User stating that this message was coming up all of a sud
den
> when she made updates to the table.
> I found the table and reactivated the raiseerror message and I get it when
I
> updaet a field. The only thing I noticed is that the field I update in th
is
> table (the master table) is not a key field. In the Detail table when I
> update the RecordID field this action does not raise the message like in t
he
> Master table. I guess my question is if this is something fundamental tha
t I
> am missing or is it something that I need to dig around to see what is goi
ng
> on?
> "Tibor Karaszi" wrote:
>|||You more or less lost me on the logic part, but from a technical viewpoint:
If you see the error when executing a statement which will result in the tri
gger being called using
TSQL but not when using ADP, then probably ADP is masking that error and you
should check with an
Access group to see how you can rectify this behavior in Access. Use Profile
r to catch the TSQL
command being submitted from Access just to make sure of what is happening o
n the server level.
Here is how you replace a message text:
EXEC sp_addmessage 50001, 16,
N'Old message text'
GO
EXEC sp_addmessage 50001, 16,
N'NEW message text', @.replace = 'replace'
GO
RAISERROR(50001, -1, 1)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
> Well, I was able to raise that message if I physically update the RecordID
-
> meaning I go to the live table in the Access ADP which is the same thing t
hat
> was going on with the Master table - where the End user was physically
> writing to the table through a form. But if I update the table
> programmatically from the ADP, then the message does not come up.
> While I am at it, I want to alter/replace my raiseerror message. I used t
he
> sp_addmessage sp. Since my message already exists as 50001, I don't want
to
> add another message. I want to alter this one. But I get an error messag
e
> in QA saying that I need to use REPLACE to alter the message for ID 50001.
I
> don't know the syntax for this. I have tried variations such as:
> USE master
> EXEC sp_addmessage 50001, 16,
> select replace('This is a test custome message', 'custome', 'custom')
> and placing REplace in other locations with no success. Any suggestions h
ow
> to do this correctly would be greatly appreciated.
>
> "Rich" wrote:
>|||Thank you for explaining how to replace a custome message.
"Tibor Karaszi" wrote:

> You more or less lost me on the logic part, but from a technical viewpoint
:
> If you see the error when executing a statement which will result in the t
rigger being called using
> TSQL but not when using ADP, then probably ADP is masking that error and y
ou should check with an
> Access group to see how you can rectify this behavior in Access. Use Profi
ler to catch the TSQL
> command being submitted from Access just to make sure of what is happening
on the server level.
> Here is how you replace a message text:
> EXEC sp_addmessage 50001, 16,
> N'Old message text'
> GO
> EXEC sp_addmessage 50001, 16,
> N'NEW message text', @.replace = 'replace'
> GO
> RAISERROR(50001, -1, 1)
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:4B003260-9016-4FE1-B324-F48A24007CD7@.microsoft.com...
>