Trigger to Auto Update

  • 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

  • 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

  • Don't do it as a trigger, do it as a stored procedure and schedule it for 1st July and 1st Jan.

  • 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