April 21, 2008 at 5:19 am
for example ,
database date 20/04/08 01:01:00
current date = 23/04/08 01:00:00
my query is
i adding 3 days with database date ie, 20/04/08 + 3 = 23/04/08
now the trigger should fire bcaz database date = current date
23/04/08 = 23/04/08
How we get this output by using trigger
now i am using this query
ALTER PROCEDURE UPDATEBOOKING2
AS
BEGIN
DECLARE @BKID VARCHAR(5),@SLNO VARCHAR(5),@BKTYP VARCHAR(5),@AGID VARCHAR(5),@AGTYP VARCHAR(2)
DECLARE CURSOR1 CURSOR
FOR SELECT A.AL_BKID,'3',A.AL_BKTYP,A.AL_AGID,A.AL_AGTYP FROM AL_BOOKFIN A INNER JOIN AL_BOOKING B INNER JOIN AL_BOOKTYPE C
ON B.AL_STATUS = C.AL_BKTID
ON A.AL_BKID = B.AL_BKID
AND A.AL_BKTYP = B.AL_STATUS
WHERE A.AL_BKTYP = 2
AND AL_BKDATE + CAST(AL_DAYMIN AS INT) <= GETDATE()
OPEN CURSOR1
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM CURSOR1 INTO @BKID,@SLNO,@BKTYP,@AGID,@AGTYP
IF @BKID <> '' OR @BKID <> NULL
INSERT INTO AL_BOOKFIN VALUES (@BKID,'3','0',@AGID,@AGTYP,GETDATE())
--UPDATE AL_BOOKFIN
--SET AL_BKTYP = 0
--WHERE AL_BKID = @BKID
--AND AL_BKTYP = @BKTYP
FETCH NEXT FROM CURSOR1 INTO @BKID,@SLNO,@BKTYP,@AGID,@AGTYP
END
CLOSE CURSOR1
DEALLOCATE CURSOR1
END
please help me to solve this problem
April 21, 2008 at 6:27 am
I'm not sure I follow.
Do you want the procedure to run at midnight? If so, you need a SQL job
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 21, 2008 at 8:21 am
I'm not clear on what you're trying to do.
If you just want to know what date is 3 days from now (which is what I'm reading the first part of your post), then take a look at DateAdd in Books Online.
As an aside, it also looks to me like your cursor could be easily replaced with one "Insert ... Select ..." command, which would make it faster and more efficient.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 21, 2008 at 10:10 pm
hi,
i want this process in trigger. that i want to take the current date from server and check to the database is it possible?
can u help me please.
by george
April 22, 2008 at 12:20 am
A trigger fires when a row is updated/inserted/deleted in a table.
From the sounds of it, you want to change data at a certain time. Is that correct?
I'm also not 100% sure I follow what you're trying. Could you explain in more detail please?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 22, 2008 at 2:21 am
while the system date will change , i want to fire a trigger in table.
for example,
if
database date + 3 = systemdate(22/04/08 01:00:00)
'trigger should be fire
else
'no action'
every day it will check when the system date will change
is it possible by trigger
April 22, 2008 at 3:15 am
A trigger fires when data in a table changes.
If you want to run something at a specific time, you need a job, set up within SQL agent. From the sound of things, you would want that to be scheduled to run at midnight each day.
I assume system date is the actual date of the system clock. Correct?
What's database date?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply