Should be quite simple - t-sql 1

[thegiraffe]

ASP programmer trying desperately to program in t-SQL!

I want to check if a certain record exists in a table when the trigger runs, just lacking the knowledge (and currently mental capacity as i'm ill) to find the answer.

I have a pending table, as jobs are confirmed (deleted) i am setting up a trigger to run the next sequence in the chain and insert this job back into the table.

I have got to one task which needs to have two jobs complete before the next set of tasks can be set as pending.

ie.
code: 124
task: enter basic client details
code: 125
task: ring client to confirm

both need confirming before we can do something, ie. send info to agency

I can set criteria etc but i don't know in SQL how to say not in table, ie.

if code = 124 had been confirmed (deleted) then i want it to check for code 125 in the table
select * from TABLE where code = 125

if 125 exists i don't want it to do anything, if it doesn't then run insert statements.

The if statements and insert statements i can do, but how do i find out if a record exists without an error.

Sorry if this makes no sence, as mentioned I am full of illness. Dave

Theres nothing worse than an idea when its the only one we have!

 

[keertanakiran2899]

Hello

Check for @@RowCount Value after the select statement

Select * from ..(TABLENAME) Where (FIELDNAME) = ..
IF @@RowCount = 0
' DO the Required action

This should solve the problem

Sid

 

[thegiraffe]

Thats brilliant cheers, i'm used to using VBscript statements in ASP, never even touched on @@RowCount before (i have a lot to learn).

Thanks Dave

Theres nothing worse than an idea when its the only one we have!

 

[JamesLean]

The EXISTS function is designed to do exactly what you want:

IF EXISTS (SELECT * FROM table WHERE code = 125)
BEGIN
  <processing here...>
END

 

[thegiraffe]

Thats cool too, i'm sure i'll find them both handy along the way.

Cheers Dave

Theres nothing worse than an idea when its the only one we have!

 

[thegiraffe]

Is there a way to do a
IF NOT EXISTS ()

Just for future reference Dave

Theres nothing worse than an idea when its the only one we have!

 

[JamesLean]

Yes - exactly as you've written it there!

(good guess if it was one ;-)

 

[SQLSister]

Incidentally the If exists is a better chouice especially if the table is large. Rowcount does the full query to get the count of rows, if exists stops as soon as it finds one record

 

[dhqube]

Take care using [if not exists]. My experience is this is incredibly slow on large tables.