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

quoted_identifier

Hi
I am getting this strange error :
Server: Msg 1934, Level 16, State 1, Procedure
dataIntegration_mergeCompanyData_prc, Line 90
UPDATE failed because the following SET options have incorrect settings:
'QUOTED_IDENTIFIER'
the problem seem to be with a table in an update statement in the proc where
it references a table in an index view. if I change the left outer join of
the update statement to an inner join, the error goes away, if I uncomment
the schema_binding in the index view, the error also goes away.
anyone know what is going on?
thanks
PAny time you access an indexed view the connection must have certain SET
settings such as QUOTED_IDENTIFIER set a certain way. Look up Indexed Views
and then Set options that affect results underneath that for details.
Andrew J. Kelly SQL MVP
"alfred" <alfred@.discussions.microsoft.com> wrote in message
news:79578F94-688A-4A17-B8BA-D6418C437AEA@.microsoft.com...
> Hi
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where
> it references a table in an index view. if I change the left outer join
> of
> the update statement to an inner join, the error goes away, if I uncomment
> the schema_binding in the index view, the error also goes away.
> anyone know what is going on?
> thanks
> P
>|||alfred (alfred@.discussions.microsoft.com) writes:
> I am getting this strange error :
> Server: Msg 1934, Level 16, State 1, Procedure
> dataIntegration_mergeCompanyData_prc, Line 90
> UPDATE failed because the following SET options have incorrect settings:
> 'QUOTED_IDENTIFIER'
> the problem seem to be with a table in an update statement in the proc
> where it references a table in an index view. if I change the left
> outer join of the update statement to an inner join, the error goes
> away, if I uncomment the schema_binding in the index view, the error
> also goes away.
> anyone know what is going on?
As Andrew said, you are performing an update that affects an indexed
view. Whenever the you work with an indexed view, these settings must
be on: ANSI_NULLS, QUOTED_IDENTIFIER, ANSI_WARNINGS, ANSI_PADDNING,
CONCAT_NULL_YIELDS_NULL and ARITHABORT. All but the last option are
on by default when you connect with any API but DB-Library.
However, for the first two settings, what applies when you run a stored
procedure is not the setting for the connection, as these settings are
saved with the stored procedure.
And very unfortunate, there are two tools for which QUOTED_IDENTIFIER
is off by default: OSQL and Enterprise Manager (the latter also has
ANSI_NULLS off by default). Therefore, if you use these tools, you
must take precautions to make sure that this setting is on. If you
use OSQL, use the -I option to turn on QUOTED_IDENFIER. If you use
Enterprise Manager to edit your procedures, simply stop doing that
and use Query Analyzer instead. QA does not have this issue, and is
better editor anyway.
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.mspxsql

quicker way of writing a LIKE query

Hi

I have two product tables in two different databases, both contain thousands of records. I have to write a query that suggests matches on similar codes, and have come up with:

SELECT TB1.product, TB2.product
FROM TB1
JOIN (select distinct product
from db2.dbo.TB2) as TB2 --this table has PK of product and warehouse
ON TB2.product LIKE '%' + TB1.product+'%'

which DOES work, but because the table have many rows,takes time to do it... is there a way of rewritting this query, so it gives a faster result?

Thanks in advance...The problem that I see is that you are using a definition that requires a table scan for TB2 in order to determine row-by-row if the value of TB1.product exists anywhere in TB2.product.

This type of search is an ugly process to implement using just a set based language like SQL. This kind of problem is why Full Text Search (http://msdn2.microsoft.com/en-us/library/ms142571.aspx) was added to MS-SQL. Beware, in that Full Text Search is definitely NOT a "free lunch", there is definitely an overhead cost associated with it.

There are other ways to speed up the process, but none of them are very pretty. My first thought is to evaluate the cost/benefit of using Full Text Search, and only to pursue other answers if you decide not to use it and really need something else.

-PatP|||i would like to see the WHERE clause using full text, please

WHERE CONTAINS( ... ??

your guidance here, pat, will, as usual, be deeply appreciated|||I'd like to see some sample data, with further clarification on what he considers a partial match.|||who said partial match?

here's some sample data showing columns which match
TB1.product TB2.product
shampoo Kerastase Resistance Bain Volumactive Shampoo Volumizing
philosophy cinnamon buns shampoo, conditioner, & shower gel
H2O Plus Sea Marine Revitalizing Shampoo
shaving Proraso Eucalyptus & Menthol Shaving Cream 150 ml.
The Art of Shaving Unscented Pre-Shave Oil
Tweezerman Badger Hair Shaving Brush|||who said partial match?LIKE implies partial matches, whether he wishes or not. And where did you get his data, or did I miss a smiley somewhere?|||And where did you get his datai made it up

his first post said that his query works

this data fits that query

are you smiley-deprived? here, have a few: :) ;) :blush: :rolleyes:|||Thanks. I needed those.|||i would like to see the WHERE clause using full text, pleaseI know... As you are fond of reminding me, you are so NOT a DBA. This one falls outside of the scope of solutions in which you like to play, it is one of those tasks where you just get the job done and move on with life.

The CONTAINS function doesn't work the way you are implying, I don't know of a completely set-based solution for this kind of problem. I would retrieve the rows from the smaller table to a client (such as VBA within a DTS package), and build a temp table of the matches or partial matches so that I could return that. You could also do it with a cursor and dynamic SQL, but that strikes me as even uglier. This is ugly, but it will perform better than the "brute force" of the LIKE approach.

-PatP|||hey

Thanks for all the replies...

I had advanced a wee bit...
Basically I have been able to cut down the amount of rows in TB1 on some factors, and dumped it into a temp table...

I will look into the Full Text Search : )

Thanks again

Quicker Cursor or Table Variable

Hi
I have a large update batch to make on our database which will run overnight
when no users are logged in.
I always use a Table Variable instead of a Cursor to conserve resources, but
in this case resources are not a problem but speed is.
Which would be quicker: cursor or table variable, also would I get a
performance benefit from running the batch within a Stored Procedure rather
than Query Analyser.
Thanks
BHave you looked at the execution plan used in your batch update. This should
pinpoint where the problem is.
Use a binary approach to this. In your batch write print statements which
will display datediff statements throughout the batch. This way you will
know which portion takes the longest.
I think you will find that local table variables with indexes (primary key
constraint) offer the best performance. You will probably also find that
using one or more stored procedures offers better performance as well.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
> Thanks
> B
>|||Ben
I'd understan you if you ask what is a difference between a table variable
and a temporary table?
How does it relate to the cursors?
Try to avoid using cursors because it may hurt a performance , insead use
SET BASED process to update a table
If you show us what you are trying to accomplish , we van suggest something
more useful.
"Ben" <Ben@.Newsgroups.microsoft.com> wrote in message
news:uz1niRiAGHA.916@.TK2MSFTNGP10.phx.gbl...
> Hi
> I have a large update batch to make on our database which will run
> overnight
> when no users are logged in.
> I always use a Table Variable instead of a Cursor to conserve resources,
> but
> in this case resources are not a problem but speed is.
> Which would be quicker: cursor or table variable, also would I get a
> performance benefit from running the batch within a Stored Procedure
> rather
> than Query Analyser.
> Thanks
> B
>

Quick question to check NULL values in input parameters in a stored procedure

Hi:

I have a stored procedure that calls 3 stored procedures. If some of my input parameters are NULL, I would like to skip the call to another stored procedure. Can you someone please help me with this? I would like to find out what is NULL, before I execute the other stored procedures. Thanks so much.

MA

check with is not null

example

If @.Var1 is not null
begin
exec proc1 @.Var1
end

If @.Var2 is not null
begin
exec proc2 @.Var2
end

If @.Var3 is not null
begin
exec proc3 @.Var3
end

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||Is there a way loop thru the parameters in one go, because in some instances I am dealing with a set of 50 or more parameters. Thanks.|||

something like this perhaps

declare @.v int
declare @.v2 int
declare @.v3 int

select @.v =1,@.v2 =3

if exists (select * from (select @.v as a union all
select @.v2 union all
select @.v3) z where a is null)
begin
print 'at least one parameter has a null value'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Going back to your initial response, which I think I will respond as an Answer to my question, because it is my best bet at this moment. I do have a quick question in reference to your first response, here it is:

If I have more than parameters, that I need to check for NULL, and if its NULL then dont execute the SP, and vice versa, how would i do that? Can i do something like this, my goal is to check/validate that if all values passed in are NULL, then dont call the sp:

IF @.CitizenshipStatusCode is null and
@.GovtIDTypeCode is null and
@.AlienID is null and
@.EmploymentStatusCode is null and
@.EmployerName is null and
@.EmployerAddress1 is null and
@.EmployerAddress2 is null and
@.EmployerAddress3 is null and
@.EmployerCity is null and
@.EmployerStateCode is null and
@.EmployerZipCode is null and
@.EmployerCountryCode is null and
@.Position is null and
@.WorkForeignPhoneExchange is null and
@.WorkAreaCode is null and
@.WorkPhoneNumber is null and
@.WorkExtension is null and
@.WorkEmail is null and
@.EmploymentYears is null and
@.EmploymentMonths is null and
@.MonthlySalaryAmount is null and
@.MonthlyRentAmount is null and
@.OtherMonthlyIncome is null and
@.ResidenceTypeCode is null and
@.CreatedPersonID is null and
@.UpdatedOn is null and
@.CreatedPersonID is null and
@.UpdatedOn is null
BEGIN
Set @.IsNull = 1
END
ELSE
Set @.IsNull = 0

|||

you could use coalesce since coalesce returns the first non null value

examples

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

select @.v ='1',@.v2 =3
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end
go

declare @.v varchar(40)
declare @.v2 int
declare @.v3 int

--will be null
if coalesce(@.v,@.v2,@.v2,null) is null
begin
select 'is null'
end
else
begin
select 'is NOT null'
end

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||thanks I think this is what I can use. Also, why do you have the word null at the end, inside the parantheses. Is that necessary? Whats the purpose of that?|||It is not necessary to have NULL at the end. If all of the inputs to COALESCE is NULL then it will return NULL anyway.