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...
