IF EXISTS Trigger QUERY

Question

IF EXISTS Trigger QUERY

mail2payan

SSCrazy

Points: 2286
More actions
April 5, 2011 at 1:21 am

#237334

Dear All,
I am facing a problem in creation of trigger. Am having 2 tables named as:
Availability
-------------------
ID | Name | Status
-------------------
1 | John | 1
2 | Lama | 0
3 | Anny | 0
-------------------
User_Available
----------
ID | Name
----------
1 | Lama
2 | Anny
----------
I want to write a trigger, where it'll check the Status from Availability as 0 and update the User_Available table. This is simple Query:
Create Trigger.....
select top 1 Name from Availability where Status = 0
..END
But if the situation comes that all the Names' status is 1, then trigger should insert Name in User_Available according to the Ascending Order of the ID.
E.g.: If all status is 1 in Availability, then John name should be updated as his ID is 1 and in ascending order.
Availability
-------------------
ID | Name | Status
-------------------
1 | John | 1
2 | Lama | 1
3 | Anny | 1
-------------------
User_Available
----------
ID | Name
----------
1 | John
----------
Hope I can make you understand my query?
Regards,
Daipayan
Software Programmer
Application has reported a 'Not My Fault' in module KRNL.EXE in line 0200:103F
----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 1

So... let's say that everyone has a Status of 1 and you suddenly insert 100 rows. Do you want John to have all 100 rows?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004341 More actions · Answer 2

Also, you need to explain which table you want the trigger on and what you want to fire it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mail2payan SSCrazy Points: 2286 More actions · Answer 3

Dear Jeff Moden,

I skip this thought of inserting 100rows at a time..:crying:

And I'll be firing the same from a TABLE name as USER_PROBLEM:

---------------------------

ID | USERNAME | PROBLEM

---------------------------

1 | USER1 | xyz

2 | USER2 | pqr

---------------------------

----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

mail2payan (4/5/2011)
Dear Jeff Moden,
I skip this thought of inserting 100rows at a time..:crying:
And I'll be firing the same from a TABLE name as USER_PROBLEM:
---------------------------
ID | USERNAME | PROBLEM
---------------------------
1 | USER1 | xyz
2 | USER2 | pqr
---------------------------

1) Not dealing with MULTIPLE ROW updates is a VERY common problem in trigger coding - and a CRITICAL flaw.

2) May I ask if this is a homework problem?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

mail2payan SSCrazy Points: 2286 More actions · Answer 5

No..not a homework. I am preparing a HelpDesk Solution for my Company..where they want to assign the call logs (by End-User) automatically to the Hardware Engineers according to their status (free/busy and if busy, then allocate according to alphabetical order).

Now I don't know how to do this automate process...:(

----------------------------------------
Daipayan
A Beginner to the World of DBMS!
----------------------------------------