CODEWITHSUNDEEP
mysqlstored-procedures
Research Development
Research Development

Reputation: 904

how to get procedure input parameters in trigger in mysql

PROCEDURE ::

CREATE DEFINER=`root`@`localhost` PROCEDURE `apply_Leave_SP`(
    in leavetypeid int ,
    in empid int,
    in reason varchar(100),
    in startdate date,
    in enddate date,
    in startsession int,
    in endsession int,
    in compoffid int,
    in mangerid int

    )
    BEGIN

    declare leavestatus int(10) default 0;
    declare optionalyHolidays int(10) default 0;
    declare listofholidays int(10) default 0;
    declare totalhours int (10) default 0;
    declare hours int (10) default 0;
    declare satsun int (10) default 0;

    declare manger_id int(10) default 0;

      select count(holiday_id) into optionalyHolidays from Parabola.holidays where holiday_type_id=2 and 
                              DATEDIFF(startdate,curdate())>=20;

        select count(*) into listofholidays from Parabola.holidays where date between startdate and enddate;                      

     SELECT 
    COUNT(*) AS total into hours
    FROM 
    (   SELECT ADDDATE(startdate, INTERVAL @i:=@i+1 DAY) AS DAY
        FROM (
            SELECT a.a
            FROM (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
            CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS b
            CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) AS c
        ) a
        JOIN (SELECT @i := -1) r1
        WHERE 
        @i < DATEDIFF(enddate, startdate)

    ) AS dateTable
    WHERE WEEKDAY(dateTable.Day) IN (5,6);

           SELECT  datediff(enddate,startdate) into totalhours; 
           set hours=(totalhours-listofholidays-satsun)*8;
        if(leavetypeid=1)then
         set leavestatus=5;
         else
          set leavestatus=1;
           end if;    

           if(optionalyHolidays>=0) then
           set leavestatus=5;
           end if; 


    insert into leave_applied(leave_type_id,hours,employee_id,
    created_at,updated_at,start_date,end_date,start_date_session,
    end_date_session,reason,
    status,reminder_count,personal_calendar_event_id,system_calendar_event_id)

    values(leavetypeid,hours,empid,curdate(),curdate(),startdate,enddate,
    startsession,endsession,
    reason,leavestatus,1,'sdasdas','sadeew');

    END

Trigger::

DELIMITER $$
CREATE TRIGGER insert_trigger
after INSERT ON leave_applied FOR EACH ROW
begin
declare leavestatus int(10) default 0;
declare optionalyHolidays int(10) default 0;
declare paidleave int(10) default 0;    


     insert into leave_actual (leave_applied_id,
     leave_type_id,hours,start_date,
     end_date,created_at,updated_at) values(new.leave_applied_id,new.leave_type_id,
     16,new.start_date,new.end_date,curdate(),curdate()) ;

    select count(holiday_id) into optionalyHolidays from Parabola.holidays where holiday_type_id=2 and
                          DATEDIFF(new.start_date,curdate())>=20;   
   if(new.leave_type_id=1)then
     set leavestatus=5;
     else
      set leavestatus=1;
       end if;    

       if(optionalyHolidays>=0) then
       set leavestatus=5;
       end if;

      insert into leave_approval (leave_applied_id,
     manager_id,status ,created_at,updated_at) values
     (new.leave_applied_id,new.employee_id,leavestatus,curdate(),curdate());

END

given code is of Stored Procedure and trigger there are 9 input parameter in SP i want get Mange-rid value in trigger but i am unable to get that value please suggest me how to get that manger id value in trigger we don't have column manger_id in to leave_applied please suggest me

Upvotes: 0

Views: 729

Answers (1)

Ivan Cachicatari
Ivan Cachicatari

Reputation: 4284

The trigger and stored procedure has no direct communication in that fact, there is not possible to obtain directly a parameter of stored procedure from trigger body.

BUT, we can use mysql variables:

All variables for a given client session are automatically freed when that client exits. 

CREATE PROCEDURE `apply_Leave_SP`([...], in mangerid int)
BEGIN
    [...]

    -- set the variable before insert
    set @connection_variable = mangerid;
    insert into leave_applied(leave_type_id,hours,employee_id, [...]

END

After that you can use the @connection_variable in your insert_trigger.

Upvotes: 2

Related Questions