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)
>