MS SQL Server - insert a row with conditions

Author

insert a row with conditions

lnoldori@hotmail.com

2006-03-24, 7:26 am

I tryed to find something about this but i couldn't so i am sorry if
i am posting something already answered.
The problem i have is that i am trying to insert a rwo in a table
only if the entries for this user doesn't exceed a certain limit.
For example :

insert into TableA values(V1,V2,V3,V4)
WHERE (select COUNT(ColumnName) FROM TableA where ColumnName=1234) <
20

is this possible?Thanks for your time and sorry for my bad english.

Razvan Socol

2006-03-24, 7:26 am

Hi,

You can use one of these:

IF (select COUNT(ColumnName) FROM TableA where ColumnName=1234) < 20
insert into TableA values(V1,V2,V3,V4)

or:

insert into TableA SELECT V1,V2,V3,V4
WHERE (select COUNT(ColumnName) FROM TableA where ColumnName=1234) <
20

But I would use a trigger on TableA to prevent the insert:

CREATE TRIGGER TriggerName ON TableA
FOR INSERT
AS
IF EXISTS (
SELECT * FROM TableA AS a
WHERE a.UserName IN (SELECT i.UserName FROM inserted i)
GROUP BY a.UserName
HAVING COUNT(*)>20
) BEGIN
RAISERROR ('Too many entries for the same user',16,1)
ROLLBACK
RETURN
END

(The code is untested, of course)

Razvan

lnoldori@hotmail.com

2006-03-27, 7:25 am

Thank you very much for your time.I Will try your solution and i will
let you know.I can not unfortunately use triggers :(

lnoldori@hotmail.com

2006-03-27, 7:25 am

Unfortunately after testing both of these queries noone is working :(
The message wich appears is

SQL execution error # 1064:Responce from database

You have an error in your sql syntax;check the manual that corresponds
to your MySql server version for the right syntax near "where (select
count(UserID From UserTable where User ..

I am using The MySql Front application to test them

Is because i am using mysql??

Razvan Socol

2006-03-28, 3:23 am

Hello,

Since this is a Microsoft SQL Server newsgroup, I assumed that you use
SQL Server. I am not familiar with the syntax for MySQL. AFAIK,
subqueries are supported by a later version of MySQL (I'm not sure
which). You should post to some other newsgroup for help with that.

Razvan

lnoldori@hotmail.com

2006-03-28, 3:23 am

You are right.Sorry for this.In future i will try to find first the
appropriate group.Thanks for your time

Sponsored Links