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

R

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!

R

How do i replace that character in a derived column ?

Some rows have that character in one or more columns. If i just write this

column == "" ? Unknown : column

where the character is inside "" (can't write it here) the task will just succes without ever doing anything. The output says something like "the dataflow task had no tasks.....", which seems like a bug.

Use a conditional statement.

If [column] contains this value, replace it with another, else leave the value alone:

[column] == "A" ? "B" : [column]|||

i know but it's not a common character. Look at the Subject of this question!!!

It's a square character -> <-

It's a non XML valid character

|||

I think thats a new line character.

If you are using an OLEDB source for your data, you can just do a trim() on the column to get rid of it.

|||

If you know the Unicode character value for it, you can use an escape sequence:

"\xhhhh"

where hhhh is the Unicode character value.

Thank
Mark

|||trim() doesn't works. It's doesn't remove the character!|||

hmm and if it's not a unicode character ?

Try putting this in a derived comlumn

REPLACE(TRIM(TXT)," ","")

the task will then complete with this in the output.

Warning: 0x80047034 at Data Flow Task, DTS.Pipeline: The DataFlow task has no components. Add components or remove the task.

|||

Use the REPLACE function, and the Unicode escape sequenece syntax Mark described.

It is a unicode character, as all comparisons are done as Unicode inside the SSIS expression parser, it cannot be anything else as far as SSIS is concerned. You need to find out what that is, and specify it in the REPLACE.

|||

Well but if the character > < is used in a replace within a dataflowtask in ssis, it automatic removes whatever flow you might have build inside that dataflow. In my opinion that seems like a bug..

if you want to do a replace in a sql task, you can't use a direct input (the sql task will then complete as if nothing was typed inside the task). You have to use a file connection for the query, so it seems like this character is the character from hell... :-)

So if you don't have the escape sequenece for this character (can't find it, since you can't search for it :-) ) you'll have to load the entire table to a temp table and do an ordinary replace in a sql query

|||

jam281 wrote:

it automatic removes whatever flow you might have build inside that dataflow.

Can you elaborate on exactly what you mean by "whatever flow you might have build".

-Jamie

|||Download any one of the many free hex editors on the Internet and open up a line of your source in it. Use the hex editor to find the hex value of the character in question. Go from there in your replace function.|||

"Can you elaborate on exactly what you mean by "whatever flow you might have build".

Allright. Try to create a new package - Add a dataflow and open it. Inside the dataflow create an oledb source - point to a table and map it.

Put in a derived column and a do a replace on one of the column like:

CENTRE == " " ? "Unknown" : CENTRE

put in a ole db destination - connect all tree

try to run it and it completes without doing anything. save the package and close the project.

Open the project again and your dataflow is now suddently empty.

The same problem apply if you have copied a sql query inside a sql task and it contains somewhere. The sql task will the execute witout doing anything...

|||

hmm found out that its a char(2) character.

select char(2)

gives that character. Now how do you replace char(2) in a derived column

|||

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

|||

Mark Durley wrote:

Let me know if using Unicode escape sequence as follows works for you:

CENTRE == "\x0002" ? "Unknown" : CENTRE

Thanks
Mark

It worked thanks!!!

Quick SQL Question - update a single row with multiple rows

This is on SQL Server 2000 (if that matters, but I think this is just a
T-SQL issue).
I have two tables (tied together by a common key, in this example TableID),
the first of which has many rows (for a given ID), and the second has just
one row (for that ID).
I want to figure out a way to do an UPDATE query (without using a cursor)
that will allow me to build up the Descr(iption) field on the second table
with all of the values from the original table, concatenated.
For example, if the first table (which you'll see I create and populate in
the example below) contains:
TableID Counter
1 1
1 2
1 3
1 4
1 5
and the second table contains:
TableID Descr
1 Start:
I want to come up with an update query that will join the two tables, and
populate the Descr field of the single row of the second table (for TableID
1) with: "Start: 1, 2, 3, 4, 5".
And yet, I'm at a loss to figure out a way to do this (other that cursors,
that I need to avoid using).
Here's my code, for what it's worth:
DECLARE @.table1 table
(
TableId int,
Counter int
)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,1)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,2)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,3)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,4)
INSERT INTO @.Table1 (TableID,Counter)
VALUES (1,5)
DECLARE @.Table2 table
(
TableID int,
Descr char(1024)
)
INSERT INTO @.Table2 (TableID, Descr)
VALUES (1, 'Start:')
UPDATE @.Table2
SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
FROM @.Table2 T2
INNER JOIN @.Table1 T1
ON T2.TableID = T1.TableID
select *
from @.Table2you want to create a udf to do concat...
e.g.
create function udf(@.id int)
returns varchar(1024)
as
begin
declare @.s varchar(1024)
select @.s=isnull(@.s+',','')+cast(@.counter as varchar)
from tb1
where id=@.id
return @.s
end
update tb2
set descr=udf(id)
-oj
"Scott M. Lyon" <scott.RED.lyon.WHITE@.rapistan.BLUE.com> wrote in message
news:ugO34pvPGHA.1556@.TK2MSFTNGP09.phx.gbl...
> This is on SQL Server 2000 (if that matters, but I think this is just a
> T-SQL issue).
>
> I have two tables (tied together by a common key, in this example
> TableID), the first of which has many rows (for a given ID), and the
> second has just one row (for that ID).
>
> I want to figure out a way to do an UPDATE query (without using a cursor)
> that will allow me to build up the Descr(iption) field on the second table
> with all of the values from the original table, concatenated.
>
> For example, if the first table (which you'll see I create and populate in
> the example below) contains:
> TableID Counter
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
>
> and the second table contains:
> TableID Descr
> 1 Start:
>
> I want to come up with an update query that will join the two tables, and
> populate the Descr field of the single row of the second table (for
> TableID 1) with: "Start: 1, 2, 3, 4, 5".
>
> And yet, I'm at a loss to figure out a way to do this (other that cursors,
> that I need to avoid using).
>
> Here's my code, for what it's worth:
> DECLARE @.table1 table
> (
> TableId int,
> Counter int
> )
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,1)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,2)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,3)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,4)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,5)
> DECLARE @.Table2 table
> (
> TableID int,
> Descr char(1024)
> )
> INSERT INTO @.Table2 (TableID, Descr)
> VALUES (1, 'Start:')
> UPDATE @.Table2
> SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
> FROM @.Table2 T2
> INNER JOIN @.Table1 T1
> ON T2.TableID = T1.TableID
> select *
> from @.Table2
>|||Scott M. Lyon wrote:
> This is on SQL Server 2000 (if that matters, but I think this is just a
> T-SQL issue).
>
> I have two tables (tied together by a common key, in this example TableID)
,
> the first of which has many rows (for a given ID), and the second has just
> one row (for that ID).
>
> I want to figure out a way to do an UPDATE query (without using a cursor)
> that will allow me to build up the Descr(iption) field on the second table
> with all of the values from the original table, concatenated.
>
> For example, if the first table (which you'll see I create and populate in
> the example below) contains:
> TableID Counter
> 1 1
> 1 2
> 1 3
> 1 4
> 1 5
>
> and the second table contains:
> TableID Descr
> 1 Start:
>
> I want to come up with an update query that will join the two tables, and
> populate the Descr field of the single row of the second table (for TableI
D
> 1) with: "Start: 1, 2, 3, 4, 5".
>
> And yet, I'm at a loss to figure out a way to do this (other that cursors,
> that I need to avoid using).
>
> Here's my code, for what it's worth:
> DECLARE @.table1 table
> (
> TableId int,
> Counter int
> )
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,1)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,2)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,3)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,4)
> INSERT INTO @.Table1 (TableID,Counter)
> VALUES (1,5)
> DECLARE @.Table2 table
> (
> TableID int,
> Descr char(1024)
> )
> INSERT INTO @.Table2 (TableID, Descr)
> VALUES (1, 'Start:')
> UPDATE @.Table2
> SET Descr = Descr + ' ' + CONVERT(char(1),T1.Counter) + ','
> FROM @.Table2 T2
> INNER JOIN @.Table1 T1
> ON T2.TableID = T1.TableID
> select *
> from @.Table2
Don't store the data in both forms in permanent tables. For one thing
you are creating redundancy. For another, "descr" looks like a
non-atomic value, which is a bad idea in principle. So assuming this is
just a one-off exercise a cursor may even be the most feasible
solution.
Assuming your data will be unchanging while you update Table2, take a
look at this example for one possible solution:
http://groups.google.co.uk/group/mi...5888972df4b3291
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1141418003.533985.216310@.e56g2000cwe.googlegroups.com...
> Don't store the data in both forms in permanent tables. For one thing
> you are creating redundancy. For another, "descr" looks like a
> non-atomic value, which is a bad idea in principle. So assuming this is
> just a one-off exercise a cursor may even be the most feasible
> solution.
> Assuming your data will be unchanging while you update Table2, take a
> look at this example for one possible solution:
> http://groups.google.co.uk/group/mi...5888972df4b3291
> --
> David Portas, SQL Server MVP
>
This was actually just an overly simplified example, so I could figure out
how to do this, and then apply that to the real problem. The real issue is
that the source tables are actually a combination of three or four permanent
tables, and the destination (that I'm doing the update on) is a temp table,
just used for generating data for reporting.|||>> The real issue is that the source tables are actually a combination of th
ree or four permanent tables, and the destination (that I'm doing the update
on) is a temp table,just used for generating data for reporting. <<
In a tiered architecture, is done in the front end and not in the
database. It sounds likeyou want to have VIEW that collects the report
data and then you can arrange it anyway you wish with the front end.
Update a temp table from several base tables, one at a time, is an
awful way to write SQL. We prefer to have things happen "all at once"
and in procedural steps.

Quick 6.5 question

Does anyone know the maximum number of rows a SQL 6.5
table can hold..
I've got an old 3rd party app suddenly throwing out lots
of these errors - 'The server could not expand a table
because the table reached the maximum size. '
Thanks in advance.Does the NT event log say, Event ID 2009'
//Ralph
>--Original Message--
>Does anyone know the maximum number of rows a SQL 6.5
>table can hold..
>I've got an old 3rd party app suddenly throwing out lots
>of these errors - 'The server could not expand a table
>because the table reached the maximum size. '
>
>Thanks in advance.
>.
>|||ok,
It doesn't neccessary need to be table in SQL server,
according to articles I found it is when creating a table
in the memory (system).
http://www.eventid.net/display.asp?eventid=2009&source=
The last section of that page you got links and
explanations about the error....
Good Luck.
//Ralph
>--Original Message--
>yes it does..
>
>>--Original Message--
>>Does the NT event log say, Event ID 2009'
>>//Ralph
>>--Original Message--
>>Does anyone know the maximum number of rows a SQL 6.5
>>table can hold..
>>I've got an old 3rd party app suddenly throwing out
lots
>>of these errors - 'The server could not expand a table
>>because the table reached the maximum size. '
>>
>>Thanks in advance.
>>.
>>.
>.
>|||Ralph, thanks - looks like a very useful link...
Cheers, steve
>--Original Message--
>ok,
>It doesn't neccessary need to be table in SQL server,
>according to articles I found it is when creating a table
>in the memory (system).
>http://www.eventid.net/display.asp?eventid=2009&source=
>The last section of that page you got links and
>explanations about the error....
>Good Luck.
>//Ralph
>
>>--Original Message--
>>yes it does..
>>
>>--Original Message--
>>Does the NT event log say, Event ID 2009'
>>//Ralph
>>--Original Message--
>>Does anyone know the maximum number of rows a SQL 6.5
>>table can hold..
>>I've got an old 3rd party app suddenly throwing out
>lots
>>of these errors - 'The server could not expand a table
>>because the table reached the maximum size. '
>>
>>Thanks in advance.
>>.
>>.
>>.
>.
>