How to make a DB to update its own Table colums

Is there any way to update a table columns automatically. For example:

We have a table(tblFirm) which columns are: FirmName,IsOpen
And another table(tblTime) which stores time information: OpenTime,CloseTime

What i want to do ; between the time intervals of OpenTime&CloseTime , DB will automatically set IsOpen's value to true, otherwise false. Is there anyway to do this?

Happy Coding...

If i am getting right, you want to Add a row in table tblFirm(with IsOpen as True/False), if the current time lies between OpenTime & CloaseTime ? if this is so, then you need to write a INSERT TRIGGRE on table tblTime, which will check the current time against the OpenTime & CloseTime, if time lies in between, then update the table tblTime with appropriate values for r FirmName & true/flase for the IsOpen.

Gurpreet S. Gill

|||

I agree partially with Gurpreet Singh Gill.

But it wont automatically set false when the current time elapsed with CloseTime.

The best solution is,
1. Create a function which will find the Firm is Open or Close from the TBLTIME table.

Create Function dbo.IsOpen(@.CurrentDate datetime, @.FirmId int) returns int
as
Begin
Declare @.IsOpen as int;
Select
@.IsOpen =
Case When @.CurrentDate <= Max(EndTime)
And @.CurrentDate >= Max(StartTime)
Then 0 Else 1 End
From
TBLTIME
Where
FirmId = @.FirmId;

Return @.IsOpen;
End

2. Change the IsOpen column of your table as Computed Column

create table TBLFIRM
(
FirmId int,
IsOpen as dbo.IsOpen(Getdate(),FirmId)
)

|||

ManiD, this seem to be the good solution. There is always a number of solution for a given problem, specially the filed in which we are.

Gurpreet S. Gill

|||

ManiD thanks for helps. Also thanks Gurpreet Singh Gill for post. Solutions ar great...

Happy Coding...