February 14, 2006 at 3:44 am
Hello Friend,
I am very new to computer programming.
We have created an application, which tracks the records of Employees' Leave. Now certain types and amount of leaves are credited in Employee's account on every 1st January and 1st July. For which I have wrote a Trigger to update the Leave account but the problem is that I am not able to figure it out how to run that trigger on perticular date & time.
Below is code of my trigger.
-----------------------------------------------------
CREATE TRIGGER [LeaveSetup] ON [dbo].[LEAVEPOL]
FOR UPDATE
AS
declare @eid int
declare @mid int
declare @mDETid int
declare @hpl int
declare @el int
declare @Cl int
declare @Chpl int
declare @Cel int
declare @Ccl int
declare @MAXhpl int
declare @MAXel int
declare @MAXcl int
declare cur cursor
for select leavepolid from dbo.leavepol open cur
fetch next from cur into @mid
WHILE (@@FETCH_STATUS=0)
BEGIN
select @mdetid=leavepolid , @eid =employeeid from leavepolid where employeeid=154
select @hpl=LEAVEBALANCE from leavepoldet where leavepolid=@mdetid and leavetypeid=3
select @CL=LEAVEBALANCE from leavepoldet where leavepolid=@mdetid and leavetypeid=15
select @EL=LEAVEBALANCE from leavepoldet where leavepolid=@mdetid and leavetypeid=16
SELECT @CCL=CARRYFORWARD , @MAXCL=MAXATATIME FROM LEAVETYPE WHERE LEAVETYPEID=15
SELECT @CHPL=CARRYFORWARD , @MAXHPL=MAXATATIME FROM LEAVETYPE WHERE LEAVETYPEID=3
SELECT @CEL=CARRYFORWARD , @MAXEL=MAXATATIME FROM LEAVETYPE WHERE LEAVETYPEID=16
SET @HPL = @HPL + @CHPL
SET @CL = @CCL
SET @EL = @EL + @CEL
UPDATE LEAVEPOLDET SET LEAVEBALANCE=@CL WHERE LEAVEPOLID=@MDETID AND LEAVETYPEID=15
UPDATE LEAVEPOLDET SET LEAVEBALANCE=@EL WHERE LEAVEPOLID=@MDETID AND LEAVETYPEID=16
UPDATE LEAVEPOLDET SET LEAVEBALANCE=@HPL WHERE LEAVEPOLID=@MDETID AND LEAVETYPEID=3
FETCH NEXT FROM CUR INTO @MID
end
close cur
deallocate cur
commit
------------------------------------------------------
Waiting for your comments
Neetal Shah
February 14, 2006 at 4:03 am
HI,
YOU CANNOT FIRE A TRIGGER ON PERTICULAR DATE OR ON A PERTICULAR TIME, TRIGGERS ARE FIRED ON INSERT OR UPDATE OR ON DELETED OPERATIONS
AGAINST THE TABLE. WHAT YOU CAN DO IS CREATE A PROCEDURE AND SCHEDULE IT ON THAT PERTICULAR DATES AND TIMES TO UPDATE THE EMPLOYEE'S
LEAVE ACCOUNTS
February 14, 2006 at 7:09 am
Don't do it as a trigger, do it as a stored procedure and schedule it for 1st July and 1st Jan.
February 14, 2006 at 7:43 am
To further explain the previous answers, you put the code that updates your data in a stored procedure. Then you can create a job (SQL Server Agent) that starts this procedure. Job can be scheduled to run automatically at certain times, and also set to notify you in case it failed to run successfully.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply