If Exists Trigger Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

If Exists Trigger Query

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2011-04-04 : 09:10:02

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:

select top 1 Name from Availability where Status = 0

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-04-05 : 04:16:48

Something like this?

CREATE TRIGGER ...
AS

SET NOCOUNT ON

TRUNCATE TABLE dbo.User_Available

IF EXISTS (SELECT * FROM dbo.Availability WHERE [Status] = 0)
	INSERT	dbo.User_Available
		(
			ID,
			Name
		)
	SELECT	ROW_NUMBER() OVER (ORDER BY ID) AS ID,
		Name
	FROM	dbo.Availability
	WHERE	[Status] = 0
ELSE
	INSERT		dbo.User_Available
			(
				ID,
				Name
			)
	SELECT TOP(1)	1 AS ID,
			Name
	FROM		dbo.Availability
	ORDER BY	ID

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources